Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aleix_rodriguez
Contributor
Contributor

Pivot table (Calculate results until the dimension)

Hi,

I've got a table dat with the following columns:

- Year

- Month

- Date

- Sales

I want to build a pivot table with the dimensions:

- Year

- Month

I would like to know the expression that evaluates the amount of sales until the dimension, not only the amount of sales tha match with the dimension.

For example:

Year 2012 Month 4: I want to calculate the sales that occurred until April 2012, no only during April 2012.

Can somebody help me?

Thanks in advance.

Aleix.

4 Replies
Gysbert_Wassenaar

See attached qvw. It has two rangesum expressions. One calculates the YTD upto the current month (i.e. not including the current month). It resets on a new year. If you need to include the current month in the sum use this expression instead: rangesum(above(sum(Sales),0,RowNo())). The other expression in the pivot table keeps counting over year boundaries. Does that give you what you need?


talk is cheap, supply exceeds demand
aleix_rodriguez
Contributor
Contributor
Author

Thank you very much Gysbert, that is what I needed.

Just one other thing, I tried the expression you gave me in your reply in order to incude the current month but it doesn't work.

Thank you very much again.

Gysbert_Wassenaar

Uhm yeah, I copied the expression and the forgot to change the 1 to a 0 (zero). It should be rangesum(above(sum(Sales),0,RowNo()))


talk is cheap, supply exceeds demand
aleix_rodriguez
Contributor
Contributor
Author

HI Gysbert,

just another question.

According to my first question, in order to have the accumulate sales, I need to have all dates (dimensions) active in the pivot table.

Do you know whether it is possible to have an expression that gives me the acumulate sales until May 2012, if in the pivot table I've only got the year 2012 even though the sales started in 2011?

Thanks a lot in advance.