topic Re: Running Totals in QlikView

Running Totals

tmumaw — Mon, 29 Aug 2016 18:10:26 GMT

I have a chart that I am trying to create a running total on. I am using this as the formula, but Jan always comes out to be 1.00 any ideas what I am doing wrong? I have for example Jan: 250, Feb: 300, Mar: 300. I am looking for Jan 250, Feb 550, Mar 850, etc.

Thanks

RangeSum(above(Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] ={'Sales Plan'}>} [Plan_Dollars])),0,RowNo())

Re: Running Totals

sunny_talwar — Mon, 29 Aug 2016 18:12:59 GMT

Try this:

RangeSum(Above(TOTAL Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1}, [Plan Description] ={'Sales Plan'}>}[Plan_Dollars])), 0, RowNo(TOTAL))

Re: Running Totals

tmumaw — Mon, 29 Aug 2016 18:21:57 GMT

Nope. Here is a sample of my data. Jan is 0

Re: Running Totals

sunny_talwar — Mon, 29 Aug 2016 18:27:38 GMT

I am not sure if the Excel explains much, but how about this:

RangeSum(Above(TOTAL Sum({1<[Plan Description] ={'Sales Plan'}>}[Plan_Dollars])), 0, RowNo(TOTAL)) * Avg({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1}>} 1)

Re: Running Totals

tmumaw — Mon, 29 Aug 2016 18:33:37 GMT

Nope. The excel file is my input. If you look at the first column 32263, is showing zero on the chart, where it should be 32263, then the next column should be 32263 + 36196 and so on.

Re: Running Totals

sunny_talwar — Mon, 29 Aug 2016 18:46:30 GMT

Are you using CrossTable Load to change the date to be a row?

Re: Running Totals

tmumaw — Mon, 29 Aug 2016 18:48:34 GMT

Yes

PLAN_DOLLARS_TEMP:
CrossTable(Periods, Data, 3)
LOAD *
FROM

(ooxml, embedded labels, table is OG);
;

PLANDOLLARS_1:
NoConcatenate
LOAD [Profit Center],
[Plan Description],
date(evaluate(Periods), 'YYYY-MM-DD') as WEDAT,
Data as Plan_Dollars,
HasPlan
RESIDENT PLAN_DOLLARS_TEMP;
DROP TABLE PLAN_DOLLARS_TEMP;

TOPSPLAN:
NoConcatenate
LOAD [Profit Center],
[Plan Description],
WEDAT,
[Profit Center] & [Plan Description] & WEDAT as ConKey,
Plan_Dollars,
HasPlan
RESIDENT PLANDOLLARS_1;
DROP TABLE PLANDOLLARS_1;

LEFT JOIN (TOPSPLAN)

LOAD DISTINCT WEDAT,
if(FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS YTDFlag,
if(FISPD = '$(vFisPd)', 1) AS MTDFlag,
if(FISPD = '$(vPrevFisPd)', 1) AS PrevMTDFlag
FROM (qvd)
where FISYR >= '$(vPrevYear)' and FISYR < '$(vYear)';

CONCATENATE (GLPCA)
LOAD *
RESIDENT TOPSPLAN;

DROP TABLE TOPSPLAN;

Re: Running Totals

sunny_talwar — Mon, 29 Aug 2016 18:51:04 GMT

Makes sense. Can you check if this alone in a chart (Straight table) with Month as dimension give you the correct values?

Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1}, [Plan Description] ={'Sales Plan'}>}[Plan_Dollars]))

Re: Running Totals

tmumaw — Mon, 29 Aug 2016 18:59:39 GMT

Straight table works...

Re: Running Totals

sunny_talwar — Mon, 29 Aug 2016 19:01:23 GMT

How about this in the straight table?

RangeSum(Above(Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1}, [Plan Description] ={'Sales Plan'}>}[Plan_Dollars])), 0, RowNo()))

Re: Running Totals

tmumaw — Tue, 30 Aug 2016 11:08:46 GMT

Sorry had a meeting the rest of the day. I got the same thing 1.00 in January and the values in Feb - July

Re: Running Totals

sunny_talwar — Tue, 30 Aug 2016 11:10:36 GMT

How about you use this expression:

Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1}, [Plan Description] ={'Sales Plan'}>}[Plan_Dollars])

and select full accumulation from the expression tab?

Re: Running Totals

tmumaw — Tue, 30 Aug 2016 11:21:11 GMT

You are the man. It works.

Re: Running Totals

tmumaw — Tue, 30 Aug 2016 11:21:34 GMT

That was a tricky one.

Re: Running Totals

sunny_talwar — Tue, 30 Aug 2016 11:23:15 GMT

I am still not sure why RangeSum(Above()) did not work. I would have expected it to work.

Re: Running Totals

tmumaw — Tue, 30 Aug 2016 11:25:33 GMT

Me too. I have a lot of other apps that use it. Might have to do some more digging and question asking to find out why. Thanks a lot for your help. I see you are a early bird too.