Combining 2 pivot tables into 1 chart

[Ramballah]

Hello everyone,

My question sounds simple but I do not know the answer.
I have 2 pivot tables and 2 charts that go with this. However I want to merge them into only 1 chart.
I have no intention of combining the raw data together because we are resetting the data from both tables weekly.
So what we want is the 2 tables and 1 chart consisting of the 2 tables. Here is my example (sorry its dutch)
Lets call the table on the left table A and the table on the right table B.
The top chart belongs to table A and the bottom chart belongs to table B.
I want to see both table A and B but only have one chart. The design for the chart im not entirely sure what looks best.
Currently im thinking about having table A go upwards in the chart and table B downwards. Or have both tables next to each other so I can see which table is higher.
I hope I didnt confuse anyone and that someone can help me with this.
Thanks in advance,
Ram

 

[Ramballah]

Like this

easyupload.io

easyupload.io

easyupload.io

Hi,
This looks like a good work around but can you tell me how you did this?
=IF(ROW()-1<(vWeeks+2);MAX(vWkStart;N(A1)+1);"")
I am unsure what vWeeks and vWkStart and N(A1) mean and how to change them for my future charts.
If vWeeks is a range which I am guessing, can you please tell me what range you took and is it possible to do normal references like 'Debiteuren'!A:A for example?
And looking at the chart, what does K€ mean?
EDIT*: Looking at this, how did you get week 5-6-7? Those weeks have nothing in them yet they appear on the chart/table

 

[GraH]

Hi,

Since weeks are a time scale I prefer to have all week intervals on my chart, making it obvious there is nothing in these weeks. Starting from this I created the range starting from the minimum week in both pivots. Then getting the second largest value, since you have the years on top. The difference between both gives me the range of weeks to populate.

See the name manager:
- [vWkStart]=MIN(MIN('Chart debtor'!$A:$A),MIN('Chart creditor'!$A:$A))
- [vWkEnd] =MAX(LARGE('Chart debtor'!$A:$A,2),LARGE('Chart creditor'!$A:$A,2))
- [vWeeks]=vWkEnd-vWkStart

So making the range of weeks to populate
- starts in A2, so ROW() returns 2. The first week can be 1, so we do minus 1.
- however the vWkStart, the first real week, can be larger. Therefore I take the max of both values.
- I populate the range for as long the ROW()-1 does not exceeds the number of weeks + 2 (to compensate for starting at second row and the last values. I could have gone with <= and only add one.).
- N(A1) + 1 will result in 1 even in A1 there is a text.

I hope that's clear.

 

[Ramballah]

Hi,

Since weeks are a time scale I prefer to have all week intervals on my chart, making it obvious there is nothing in these weeks. Starting from this I created the range starting from the minimum week in both pivots. Then getting the second largest value, since you have the years on top. The difference between both gives me the range of weeks to populate.

See the name manager:
- [vWkStart]=MIN(MIN('Chart debtor'!$A:$A),MIN('Chart creditor'!$A:$A))
- [vWkEnd] =MAX(LARGE('Chart debtor'!$A:$A,2),LARGE('Chart creditor'!$A:$A,2))
- [vWeeks]=vWkEnd-vWkStart

So making the range of weeks to populate
- starts in A2, so ROW() returns 2. The first week can be 1, so we do minus 1.
- however the vWkStart, the first real week, can be larger. Therefore I take the max of both values.
- I populate the range for as long the ROW()-1 does not exceeds the number of weeks + 2 (to compensate for starting at second row and the last values. I could have gone with <= and only add one.).
- N(A1) + 1 will result in 1 even in A1 there is a text.

I hope that's clear.

Alright, I understand that part now. Do you think its possible to leave out the week numbers I dont use? That way it looks more calm... I have no need to see weeks that have nothing in them...
Let me know if that is possible (I'm also sure my colleagues do not want to see 50 weeks at one time since we only use about 20-30 at one time)