Running Total By Month

[#1]

Hello Everyone,

I am trying to find Running total from daily Data (Same as MTD Calculated in Load Script).  I can get data by rangesum but I am stuck on my Running total should reset on the first Business date of the month.

1.  Running sum by month should reset to first business date of the month

2.  Even though the trending day changes it should Calculate correct Running Sum starting from Begining of the month.

3. In the currect selection I have selected trending days 30. So my range of data shown is correct in the table from 4/16/2014 to 

   5/15/2014.

4. For 4/16/2014 it should be 720 as highlighted in Data File

5. on 5/1/2014 it should be 8

6. on 5/9/2014 it should be 359

7. For 5/15/2014 it should be 635

Please see attached sample Data and QVW. I hope I explained it properly.

Thank you all in advance.

[#2]

Hi,

I believe this might be what you wanted.

See the attached file, I inserted the MonthStart function into your Daily and Running Sum Table. Let me know if this works for you.

[#3]

Hi Jacob,

Thank you, Ths is not i am looking for,

1. Please make selection of date as 05/15/2014, th should see data for 30 days date range that between 04/16/2014 to

   05/15/2014 and fo 4/16/2014 Running sum should be 720, which be sum of days starting from 04/01/2014 till

   04/16/2014 which is 720 but first date shown in the table(Chart) should be 04/16/2014.

2. The running total on 5/15/2014 should be 635, which is sum of all daily starting from 5/1/2014 to 5/15/2014

So basically it is a window by( Partition by) Month,

I think in Rangesum(above()) the starting and End point has to be calculated Dynamically

I hope I am clear.

Thank you

[#4]

if you create a Month in your script you can use:

Sum(Total <Month> {< TrendPeriod={">$(=Date(Max(TrendPeriod)-$(vtrendingdays)))<=$(=date(Max(TrendPeriod)))"}>}

If(TrendPeriod >= MonthStart(TrendPeriod) and TrendPeriod <= MonthEnd(TrendPeriod),([Sales Daily])))

[#5]

Hi Rahul,

Thank you,

It doesn't work... I am getting just one Number for all rows.

[#6]

try:

Sum(Total <Month> {< TrendPeriod={">=$(=MonthStart(Date(Max(TrendPeriod)-$(vtrendingdays))))<=$(=date(Max(TrendPeriod)))"}

    >} [Sales Daily])

[venkatg6759]

Running Total Calculation

Check the above post .it might be helpful

[#8]

Hi Rahul,

No Luck, Please see attached... ! No sure what am I missing

[#9]

Hi Venkat,

Thank you

I can not Do it in Load script, I have to check lot of conditions for Previous record, I posted sample data, If I can get it with Set Analysis/ Exression than it will be great...

[#10 · Accepted solution]

Hi Pat,

I think I have solution you are looking for, Please see attached.

The main trick was to get the Count in Above function. I had to add Holiday as Number of Days were not matching, I excluded Holidays from diff.

There might be elegant solution but this is what i could get in quick time...

Please Review it and let me know.

Thanks

Devang