show only in total in pivot table (QV9)

[martijn]

Hi all,

I want to build a pivot table with the following layout:

	January	February	Etc..	Total
Costnumber	Revenue	Revenue		Revenue	Budget
100000	100	150	0	250	300
100050	150	50	0	200	150
Etc	Etc	Etc	Etc	Etc	Etc

I want to see the budget only in the last total column, not for each month. I've been searching the forum and QV for an answer, but I can't seem to find one. Does anyone no if this kind of table is possible?

Thanks for your reply

Regards,

Martijn

[#2]

Hi

Add your month and CostNumber as i dimension with the Expression Revenue and Budget.

Once you have created the Pivot table, drag the Months to the top, you wll see it makes a Blue line with an arrow, then it will show the months on the top, the only thing is you are going to have Revenue and budget per month, not like you specified, revenue for month and budget as total for the Year.

The go and look at the presentation, you will be able to change the sum values there to get totals.

Hopes this helps.

[martijn]

Hi JKillian,

I'm able to make the pivot table. That's not the problem. I have the totals. I just don't want to display the budget for each month...

Regards,

[#4]

This isn't easy, and during a little play I came across 2 problems:

1- No way to suppress an expression in a pivot chart

2- No way to suppress a column if the results are all null. So....I have to fudge it!

Here is an example. Its not that elegant since the totals have been concatenated together. Using "columnno =0" I have been able to tell QlikView to calculate the sum of revenue or revenue and budget (for the total column).

Not sure if this would help?

ps- my total calculation is the wrong way around (should be 150, 300 on the first row and 300, 150 on the second)

[#5]

So to get this right you want to display the Revenue per month but the Budget on year?

Why don't you show the Budget per Month as well as the report will make more sense that way.

Or you need to show them both per Year, or you are going to have 2 objects.

[#6]

Hi Sorry

Where do i Specify columbno = 0

[martijn]

@_qlikbox: I was hoping there was an other solution... but concatenating is a solution...

@JKillian: It is not up to me to make the conclusion if a report will make more sense when the budget is also displayed per month. From customers perspective it is to much information. They want to see the progress of the revenue per month and then the total versus budget..

[#8]

@_qlikbox

can you post your example as qvw, can't follow your comments and would like to know how you made this work out.

Thanks

Juerg

[#9 · Accepted solution]

Hi,

Here you are! I woudl prefer it to be more elegant, but it is what it is!

[#10]

Hi

Thanks a lot, was not aware that Columno() = 0 can be used.

Still a bit puzzled about the Totals Column Header, why does it show "Total Revenue Budget"?

Regards

Jürg