topic Re: Re: Running Total By Month in QlikView

Running Total By Month

Fri, 06 Jun 2014 14:20:44 GMT

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.

Re: Running Total By Month

Fri, 06 Jun 2014 14:32:48 GMT

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.

Re: Running Total By Month

Fri, 06 Jun 2014 14:53:15 GMT

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

Re: Running Total By Month

Fri, 06 Jun 2014 15:02:11 GMT

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

Re: Running Total By Month

Fri, 06 Jun 2014 15:19:15 GMT

Hi Rahul,

Thank you,

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

Re: Running Total By Month

Fri, 06 Jun 2014 15:24:11 GMT

try:

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

>} [Sales Daily])

Re: Running Total By Month

venkatg6759 — Fri, 06 Jun 2014 15:27:53 GMT

Running Total Calculation

Check the above post .it might be helpful

Re: Re: Running Total By Month

Fri, 06 Jun 2014 15:46:46 GMT

Hi Rahul,

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

Re: Re: Running Total By Month

Fri, 06 Jun 2014 15:56:12 GMT

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...

Re: Running Total By Month

Fri, 06 Jun 2014 18:59:28 GMT

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

Re: Running Total By Month

Fri, 06 Jun 2014 19:06:38 GMT

Hello Devang,

Thank you so Mauch,

This is what exactly, I was looking for,

Amazing!!!!, Really appreciated.

Thank you so much...

Pat