topic Need an Expression for Full Accumulation/Cumulative in a Pivot table in QlikView

Need an Expression for Full Accumulation/Cumulative in a Pivot table

Wed, 29 Aug 2012 15:47:13 GMT

I have a Table with a Cost Type Column containing: Obligated, Actual's and Projected across a Month Dimension ranging from Jan-2012 into Feb-2013 a rolling calendar so YTD wont be useful. I just need a 'simple' Cumulative running total based off my set analysis sample below.

I need get have a Cumulative summation from Cost type Obligated & Actual. See Image below for ref.

The problem is Full accumulation only works in a Chart not a table. I'm new at this so I need a good starting point.

My Measure is : Amount

The Expression I use to display the Obligated Columns is: Sum({$<[Cost Type]={"Obligated*"}>}Amount)

This works fine. But if I want to compare my Actuals against my Obligated Funds which may not be in that Month then its shows I ran out of Funds even though they were allocated in Months past. The end goal is to color code the areas were the Cumulative is < less than the Cumulative Actual's for that month then flag it red.

Cumulative Obligated < Cumulative Actual's Then make Color background Red.

Thank you for any assistance rendered.

Brian

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

whiteline — Wed, 29 Aug 2012 16:44:58 GMT

I think this expression will give you accumulation of Sum({$<[Cost Type]={"Obligated*"}>}Amount) by month:

=rangesum(Before(Sum({$<[Cost Type]={"Obligated*"}>}Amount),0, ColumnNo()))

You should use "before" and ColumnNo() because your table is right foward.

For vertical accumulation use "above" and RowNo().

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thu, 30 Aug 2012 13:23:18 GMT

Everything works great, it replicates the full accumulation. I did run into a User selection issue that 'breaks' the calculation though. Wondering if there is a work around?

Since its based on right forward, if the user selects months after the obligated was allocated it never gets pulled in.

If I change the Set analysis to 1 vs $ that works by forcing it to keep ColumnNo left of the selection in place, but prevents user from de-selecting the ColumnNo. Is there a way I can ignore the month selections in the RangeSum expression or do something in the script which does not allow the Before clause.

Im thinking about ignoring the Month field in the Summation portion ?

=Sum({$<[Cost Type]={'Obligated'},Month=>}Amount)

Or Alter the Dimension Month?

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

whiteline — Thu, 30 Aug 2012 13:45:16 GMT

Thats right the solution with "before" (and so on) works with pivot itself.

So it accumulates only values that you see in pivot.

Describe your case in an example and I'll try to help you.

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thu, 30 Aug 2012 14:07:30 GMT

Maybe I need to change the Offset of 0 to a positive N to look further left. ColumnNo can only return what's available to it, so there lies the problem, just not what I need to alter to inlcude all columns even if a user slevts less than that.

Specifying an offset greater than 1 lets you move the evaluation of expression to columns further to the left of the current column. A negative offset number will actually make the before function equivalent to a after function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current column. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table columns counting to the left from the original cell. In this form, the function can be used as an argument to any of the special range functions.

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

whiteline — Thu, 30 Aug 2012 14:12:26 GMT

I would say rather "before' function "can only return what's available to it".

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

whiteline — Thu, 30 Aug 2012 14:59:08 GMT

Ok, could you please describe what behaviour you expects from your application ? (the use case).

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thu, 30 Aug 2012 15:49:46 GMT

Basically If I select 3 months Jun-Aug it should show just the three months just like the Chart does.

But with the set analysis at 1 vs $ it blocks that ability to select combinations that don't start at the beginning.

In order for the expression to work for the Color coding if The Actual's are greater than the running amount of the obligated.

If the Accumulation is only for those 3 months vs going to the beginning of the data set i.e Jan-12 then you wont get an accurate comaprison.

So its the user vs the Expression... If the user selects the montsh int eh Chart that overides me hiding the Month List box.

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

whiteline — Thu, 30 Aug 2012 16:32:01 GMT

You can try

Only({$} rangesum(Before(Sum({$<[Cost Type]={"Obligated*"}>}Amount),0, ColumnNo())))

The only function reduces the resulting set but I'm not shure if it right in your case.

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thu, 30 Aug 2012 16:59:45 GMT

Just tried it. It returns Null values blanks. Reduce I want to keep it open to maximum. The chart accumulation behaves the same way even with the built in accumulation. I guess the only way around it is to generate the accumulation in the data source or script.... But for now I'll make this work with the {1} in my set analysis.

The other Issue I ran into is to get the Line to start dash line style in Jul which appears in Aug, Even though its the end of Jul it starts the dash fro Projected .If I coould get it to move back one month.

S2=Dash S1=Line

If(Sum({$<[Cost Type]={"Actual*"}>}Amount) <= 0 ,'<S2>','<S1>')

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

whiteline — Thu, 30 Aug 2012 17:50:03 GMT

Oh, thats simple, use:

below(Sum({$<[Cost Type]={"Actual*"}>}Amount)) <= 0

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thu, 30 Aug 2012 17:55:06 GMT

Makes sense. For some reason though it went all solid.

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

whiteline — Thu, 30 Aug 2012 18:04:32 GMT

I've tried it before post.

If(below(Sum({$<[Cost Type]={"Actual*"}>}Amount)) <= 0 AND Sum({$<[Cost Type]={"Actuals*"},$(vLastMonth)={"<$(vNextMonth)"}>}Amount) <= 0,'<S2>','<S1>')

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thu, 30 Aug 2012 18:06:25 GMT

Ok, that did work. I just had to insert it properly in my If statement. Awesome such a simple and elegant solution.

I really need to start knowing the Chart Inter Record functions!

Thanks So much this one has been bugging me for three days.

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thu, 30 Aug 2012 18:09:03 GMT

This one did the trick....

If(Below(Sum({$<[Cost Type]={"Actual*"}>}Amount)) <= 0 ,'<S2>','<S1>')

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thu, 30 Aug 2012 18:10:19 GMT

This is the one that works: If(Below(Sum({$<[Cost Type]={"Actual*"}>}Amount)) <= 0 ,'<S2>','<S1>')

The one with vMonths was my failed logic I was playing with. the 'below' function is such a better method maybe the only method!

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

whiteline — Thu, 30 Aug 2012 18:11:07 GMT

Thank you, 'cause I've met that several times before and today I've finally realised how to beat that

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thu, 13 Sep 2012 19:58:58 GMT

I have a dilemma, I'm trying to solve on my pivot table. My Cumulative is showing data 2 months past where it should? See below....

My Actual Cumulative should stop at September but it keeps going along with my Obligated which should not either. Any pointers? Is this expected behavoir? See attached Image

RangeSum(Before(Sum({1<[Cost Type]={"Actual*"}>}Amount),0, ColumnNo()))

Thanks!

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

whiteline — Fri, 14 Sep 2012 06:11:56 GMT

Yes its expected.

For every column after September your expression can be calculated clearly. And its logically right. There is no actual after September so the result is the same but it exists.

You have to add a condition to make it zero in future if you want, for example:

Count({1<[Cost Type]={"Actual*"}>}Amount) > 0

Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Fri, 14 Sep 2012 12:29:19 GMT

Ok, that makes sense now. I had a feeling that would be the case. So I need to nest the Count expression you provided into my RangeSum it sounds like?

Or do I use an If statement and use the count to qualify the Rangesum before its used?

=If( Count({1<[Cost Type]={"Actual*"}>}Amount) > 0 , RangeSum(Before(Sum({1<[Cost Type]={"Actual*"}>}Amount),0, ColumnNo())) , 0 )