Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Valued Contributor

Running Totals

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())

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Running Totals

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?

View solution in original post

15 Replies
Highlighted

Re: Running Totals

Try this:

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

Highlighted
Valued Contributor

Re: Running Totals

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

   

Highlighted

Re: Running Totals

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)

Highlighted
Valued Contributor

Re: Running Totals

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.

Highlighted

Re: Running Totals

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

Highlighted
Valued Contributor

Re: Running Totals

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;

Highlighted

Re: Running Totals

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]))

Highlighted
Valued Contributor

Re: Running Totals

Straight table works...

Highlighted

Re: Running Totals

How about this in the straight table?

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