ROLLING SUMS BASED ON IF CONDITION

Thu, 13 Jul 2017 10:39:55 GMT

I have a excel where we upload monthly data to the existing file with the default dates on the file. The file contains default dates and balance amounts based on a relationship id. I want to calculate the sum of balances every month and also see how much has been increased or decreased from last month updated file. I have 2 years worth of data starting Jan-2016 to Current month(June-2017) data.

Example: I need to calculate all the sum of sales starting Jan-2016 to Jun-2017 sum of sales and also find the difference between the sum of sales between Jan-2016 to Jun-2017 and Jan-2016 to May-2017.. this has to continue every month. i.e sum of all sales year to date and month to date and difference by -1 month.

=if((sum({<[DEFAULT_DT ])] = {">= $(=Min([DEFAULT_DT ])) < $(=Max([DEFAULT_DT ]]))"}>} and SUM(BALANCE)<0),'(' & '£'& Num(SUM(if(KEY_RANK = 1, BALANCE,0))*(-1)/1000000000 ,'#,##0.00'),
'£'& Num(SUM(if(KEY_RANK = 1, BALANCE,0))/1000000000,'#,##0.00')) & 'bn' & ')'

Re: ROLLING SUMS BASED ON IF CONDITION

marcus_sommer — Thu, 13 Jul 2017 14:31:17 GMT

Take a look on this: Calculating rolling n-period totals, averages or other aggregations.

- Marcus

topic ROLLING SUMS BASED ON IF CONDITION in QlikView

ROLLING SUMS BASED ON IF CONDITION

Re: ROLLING SUMS BASED ON IF CONDITION