topic Re: Cumulative calculation with set analyses, ignoring selection (example included) in QlikView

Cumulative calculation with set analyses, ignoring selection (example included)

Thu, 28 Feb 2013 15:16:01 GMT

Hi,

I'm trying to generate cumulative results over the total period of time, even if there are some selections made.

For example. if the user selects the last 3 weeks of a year, I still want to see the total value over all time.

The user shuold be able to make other selections though, like on article level.

week	value	cumm value
2012.w52	10	10
2013.w1	10	20
2013.w2	10	30

in the above selection, if the user selects week 2013.w1 & week 2013.w2, the cumm value should still be 30.

An example file with data and selections is included.

Who can help? Thanks a lot!

Re: Cumulative calculation with set analyses, ignoring selection (example included)

Gysbert_Wassenaar — Thu, 28 Feb 2013 15:28:12 GMT

Afaik you can only do that if you calculate the running total in the script. See attached example

Re: Cumulative calculation with set analyses, ignoring selection (example included)

Thu, 28 Feb 2013 15:35:59 GMT

Hi,

I've been trying to calculate that way in the large script. I'm running into the issue that there are many side dimensions though. The example in my script is the "Article A and Article B". Cause that really should affect the grand cummulative total.

Am I going to have to take all those into account too in the script? Or is there another sollution?

Re: Cumulative calculation with set analyses, ignoring selection (example included)

Thu, 28 Feb 2013 15:56:21 GMT

Hey there,

If you want to do this as an expression in a chart try this

Sum({$<Year =,YearWeek=,WeekNumber=>} Value)

That calculates the totals for those fields and also allows you to make selections on other dimensions

-ART

Re: Cumulative calculation with set analyses, ignoring selection (example included)

julian_rodriguez — Thu, 28 Feb 2013 16:02:44 GMT

Pherhaps you can use

Sum(ALL Value)

It returns the Sum of all dimensions too.

regards,

Re: Cumulative calculation with set analyses, ignoring selection (example included)

Thu, 28 Feb 2013 16:16:10 GMT

I think that you might want to try:

rangesum(first({$<Year =,YearWeek=,WeekNumber=>} Value,1,RowNo())).

You might need to fiddle with it to make sure that RowNo() is returning the right result, though.

Just tried it on your sample set, that apparently only works on pivot tables.

Re: Cumulative calculation with set analyses, ignoring selection (example included)

Tue, 05 Mar 2013 09:25:46 GMT

Yes, but this does not provide the cumulative calculation I'm looking for. (See top table an example file)