Running Totals

[tmumaw]

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

[sunny_talwar]

Try this:

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

[tmumaw]

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

   

[sunny_talwar]

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)

[tmumaw]

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.

[sunny_talwar]

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

[tmumaw]

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;

[sunny_talwar]

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

[tmumaw]

Straight table works...

[sunny_talwar]

How about this in the straight table?

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