Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Multidimensional running totals when there aren't values for all combinations

!I'd appreciate any help the Community could provide for this situation.

I am looking to display running totals for a calculation that requires details from a specific granularity.  Because of this, I am using AGGR and the appropriate fields.  I understand that I need to utilize the RANGESUM and ABOVE functions but run into trouble when data doesn't exist for every combination of my aggregated dimensions.

The expression is:  sum(Numerator) / sum(Denominator)

Here is the data I'm using to illustrate my issue:

LOAD * Inline

[

Period,Month,Category,Type,Detail, Denominator,Numerator, Other

Period 2014-2015,1,Apple,County,misc 01,125, 3 ,0.075

Period 2014-2015,1,Apple,County,misc 02,125, 3 ,0.075

Period 2014-2015,1,Banana,County,misc 06,115, 1 ,0.07

Period 2014-2015,2,Banana,County,misc 14,120, 4 ,0.07

Period 2014-2015,3,Apple,County,misc 17,130, 2 ,0.075

Period 2014-2015,3,Banana,County,misc 20,120, 5 ,0.07

Period 2014-2015,3,Banana,County,misc 21,120, 2 ,0.07

Period 2014-2015,1,Apple,Local,misc 03,115, 5 ,0.05

Period 2014-2015,1,Banana,Local,misc 07,130, 1 ,0.05

Period 2014-2015,1,Banana,Local,misc 08,115, 5 ,0.05

Period 2014-2015,2,Apple,Local,misc 10,120, 3 ,0.05

Period 2014-2015,2,Banana,Local,misc 15,135, 5 ,0.05

Period 2014-2015,3,Apple,Local,misc 18,110, 4 ,0.05

Period 2014-2015,3,Banana,Local,misc 22,135, 1 ,0.05

Period 2014-2015,3,Banana,Local,misc 23,135, 2 ,0.05

Period 2014-2015,1,Apple,National,misc 04,100, 2 ,0.15

Period 2014-2015,1,Apple,National,misc 05,110, 2 ,0.15

Period 2014-2015,1,Banana,National,misc 09,105, 1 ,0.1

Period 2014-2015,2,Apple,National,misc 11,115, 3 ,0.15

Period 2014-2015,2,Apple,National,misc 12,105, 1 ,0.15

Period 2014-2015,2,Apple,National,misc 13,110, 4 ,0.15

Period 2014-2015,2,Banana,National,misc 16,125, 2 ,0.1

Period 2014-2015,3,Apple,National,misc 19,110, 2 ,0.15

Period 2014-2015,3,Banana,National,misc 24,125, 2 ,0.1

Period 2014-2015,3,Banana,National,misc 25,125, 1 ,0.1

];

This line chart shows (correctly) the month-by-month calculated results for "Apple":

monbymon.png

This next line chart shows my efforts at a running sum, but is incorrect for Month 2.  I think it's because there is no County data for "Apple" in Month 2:

runningmon.png

Here is the expression I attempted to build:

sum(aggr(rangesum(above( sum(Numerator) / sum(Denominator),0,RowNo(TOTAL))),Period,Category,Type,Month))

I'm including the QVW file if that will help.  The "Banana" category works as expected.  Again, I suspect this is because there is data for all types (Local, County, and National) in all months.

Thanks in advance for the great ideas!!

Eric

Message was edited by: Eric Wissner to reflect issues that exist even after Clark's suggestion below. Thinking through it more, I wonder if the whole "Type" dimension is getting nulled out in the AGGR because it's trying to divide by zero in those months where there are no County types? This would maybe explain why Month 2's result above (11.177%) is equal to the correct result when I only select Local and National types.

Tags (3)
2 Replies
Not applicable

Re: Multidimensional running totals when there aren't values for all combinations

Does this yield the expected results?


sum(aggr(rangesum(above( sum(Qty * (Price + Price * Tax)),0,RowNo(TOTAL))),Period,Category,Month))

Not applicable

Re: Multidimensional running totals when there aren't values for all combinations

Clark,  Thanks for the quick reply.  It does make that expression work, but unfortunately, I'm not having success applying it to my specific situation.  I thought I had replicated the situation/issue well in my question, but obviously hadn't.  Sorry about that.

I alluded to it in the OP, but I need to maintain/sum the results of the expression at a level that still includes Type.  My real-life expression is a division statement and we're adding the ratios that are returned for each Category and Type within each Period. 

I'm going to try and upload another version of my example app that hopefully better illustrates my issue.  If I simply remove "Type" from my AGGR statement, it isn't returning the correct granularity for the ratios that need added.

Thanks again. !