topic Cumulative Value in a pivot in QlikView

Cumulative Value in a pivot

m4u — Wed, 29 Jun 2011 10:41:37 GMT

Hi all,

An important issue tahat i have been encountered a lot of times during use of qlikview, and I cannot find any solution.

my pivot table displays a financial report, with 2 dimensions: [category] and [month + year]. my expression shows the amount of a specific category per month.

i need to change my total column that should display the cumalative value from begining of the year (see table below).

i tried to use set analisys in my total formula, for example: sum( {$<MonthYear = >} Sales ),

but this affects the whole pivot: all months will be shown on my pivot regardless the selections

if found 2 solutions but they are very complicated:

1. to remove the month dimension from the pivot, and use set analisys on all columns (on this way the user canot simply select which months to show)

2. to bring an open balance for each month, manipulated on the load script. that way I don’t need to use set analisys to lookup outside the pivot range.

But this is very complicated In some situations.

there is a better/simpler idea?

thanks,

shlomo

Category	03-2011	04-2011	Cumulative value from begining of year
A1	100	200	700 (this ammount is from begining of the year)
A2
A3
A4

Cumulative Value in a pivot

nagaiank — Wed, 29 Jun 2011 10:58:56 GMT

Did you consider having three dimensions (category, year, month) instead of two dimensions (category, year-month)?

If you have three dimensions, the partial total for the year will give the total you want.

Cumulative Value in a pivot

m4u — Wed, 29 Jun 2011 11:22:17 GMT

thanks,but this will not solve my problem becuase:

1. i need the a specific structure

2. even if i add the year dimension - if only 2 months will be selected, the year partial sum will include only the selected month's and not from beginging of the year

Re: Cumulative Value in a pivot

nagaiank — Wed, 29 Jun 2011 12:10:34 GMT

If you calcualte the cumulative total in the load script, the value will be independent of month selections. You can find a way to display that column always when two other months are selected.

An example is attached.

If the amounts have to be sensitive to selections other than months, this method will not work, as the cumulative total is calculated in the load script for the {1} set, not taking into account the user selections in the applications.

Cumulative Value in a pivot

m4u — Wed, 29 Jun 2011 14:14:54 GMT

Thank you for the example.

your solution is good only if you always select until the last period, but if you select "02-2011" the the total column shows the cumulative up to the end of year and not up to the end of the selected period.

what i did, i have generate another table on load script and get for each period, the cumulative balance for this period - this table is also attached to the period dimension so i can include it in the total fomula - but this is very complicated, and problematic in some cases, and i'm locking for a better way.

thanks.

shlomo