topic Range Sum Iterate Over Years in QlikView

Range Sum Iterate Over Years

Wed, 27 Jun 2012 10:11:22 GMT

Hi Guys,

Please can one of you help me figure this one out.

Dimension = Date

Expression = (sum(TOTAL {$<AccountLevelDesc={'Turnover'},FinancialYear={'2013'}>}CostCentreOBGValue) -

RangeSum(Above(TOTAL Sum({$<AccountLevelDesc={'Turnover'},FinancialYear={'2013'}>}CostCentreOBGValue), 1, Rowno(TOTAL))))

The expression above does exactly what it should do for 2013. It takes the total Revenue Value for the Year and then subtracts the previous accumulated amount. So for example and to illustrate a little more clearly. I have this table

Date, CostCentreOBGValue

Jan 13, 1200

Feb 13, 1100

Mar 13, 1000

Apr 13, 900

May 13, 800

Jun 13, 700

Jul 13, 600

Aug 13, 500

Sep 13, 400

Oct 13, 300

Nov 13, 200

Dec 13, 100

When i chart this the value i see in Jan 2013 with the above expression will be sum of all values for the year and minus any previous value that has past. So Month 1 shows 7800. Then in month 2 Jan has past which had a value of 1200, so this is minused from the previous value of 7800 giving us 6600. For month 3 Mar, the new accumulated past values is 2300 and takes this away from 7800 giving us 5500 and so on till eventually in the last month u have 100.

Everything is working great except i dont want to limit this expression to a single year. I want to apply this methodology across all possible years but at the end of each financial year it starts again. So when 2014 starts it takes the next 12 months and does the same diminishing principle.

Is it possible to do this. BTW the when charted it should look like a jigsaw, starts big, gets small and gets big again with the start of the new. Please can you guys let me know, been struggling with this one

Cheers,

Byron

Re: Range Sum Iterate Over Years

Thu, 28 Jun 2012 07:46:59 GMT

Anyone? I also just realised that the above calculation isnt actually correct as i am creating a stacked chart with the different business units as the other dimension, it tends to add other values when more then one business unit is selected????. If anyone knows how to do accumulation sum ranges effectively, please post

Thanks,

Byron

Re: Range Sum Iterate Over Years

Jason_Michaelides — Thu, 28 Jun 2012 08:14:59 GMT

You'll need to remove the year portion of the set analysis (only calculated once per object) and adjust the last paramter of Above() to only go up the number of date values you have (can you use a static number - 12 instead of RowNo()?)

If you can't work it out from this please post your app so we can take a look.

Hope this helps,

Jason

Re: Range Sum Iterate Over Years

Thu, 28 Jun 2012 12:52:52 GMT

Hey Jason.

Struggled for a long time with this one and below is the solution that finally worked, took me an incredibly long time to get this right. Below is the code that I used. Removing financial year as you mentioned was important and adding it to the aggr function when doing the rangesum seemed to do the trick. Thanks for your help mate

=if(FinancialYear=Min(TOTAL <CostCentreName>Year(Date)),
Sum(TOTAL<CostCentreName>{$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue),

Sum(TOTAL<CostCentreName>{$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue)-
aggr(RangeSum(Above( Sum({$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue),
1, Count(CostCentreName))),
CostCentreName,FinancialYear))

Re: Range Sum Iterate Over Years

Thu, 28 Jun 2012 13:31:52 GMT

I forgot to add, the above is for when you have two dimensions, CostCentre and Financial Year... If you wanted to add another level down i.e Date, you use the following code...

=if(Date=Min(TOTAL <CostCentreName>Date),

Sum(TOTAL<CostCentreName,FinancialYear>{$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue),

Sum(TOTAL<CostCentreName,FinancialYear>{$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue)-

aggr(RangeSum(Above( Sum({$<AccountLevelDesc={'Turnover'}>}CostCentreOBGValue),

1, RowNo(Total))),CostCentreName,FinancialYear,Date))

Cheers,

Byron