Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jsaifuddin
Contributor II
Contributor II

Daily running total for each month

Hello,

I would like to have a running total on daily basis for each month.

  

2018-01-01978.29978.29
2018-01-021516.322494.61
2018-01-031524.514019.12
2018-01-041633.255652.37
2018-01-052265.947918.31
2018-01-061792.149710.45
2018-01-071983.2511693.7
2018-01-082697.4814391.18
2018-01-092681.0517072.23
2018-01-102584.8119657.04
2018-01-112519.4922176.53
2018-01-122360.5124537.04
2018-01-132021.5726558.61
2018-01-142125.6828684.29
2018-01-152528.1431212.43
2018-01-162744.9333957.36
2018-01-172759.9936717.35
2018-01-182672.5239389.87
2018-01-192520.7641910.63
2018-01-202111.1244021.75
2018-01-212326.8946348.64
2018-01-222794.6649143.3
2018-01-232863.1152006.41
2018-01-242707.1954713.6
2018-01-252847.2957560.89
2018-01-262855.9660416.85
2018-01-272350.662767.45
2018-01-282345.9465113.39
2018-01-29281367926.39
2018-01-303034.9270961.31
2018-01-312743.3173704.62
2018-02-012399.252399.25
2018-02-021790.34189.55
2018-02-031482.11

5671.66

As you see that the running total is reset at the start of February.

I want to implement this.

I am currently using this expression which provides me cumulative sum from January to August

rangesum(above(total sum(Spend),0,NoOfDays))


Looking forward to your response.

Thank you in advance

1 Solution

Accepted Solutions
jsaifuddin
Contributor II
Contributor II
Author

Hi Stefan,

Thank you for alternate solutions.

I was able to solve it by implementing it in the load script and using sortable aggr on the front end to reset at the start of each month.

I implemented the following in my script:

if(peek(%MonthKey)=%MonthKey,rangesum(peek(CumulativeSpend),Spend),Spend) as CumulativeSpend

On front-end I used the following expression:

=sum(aggr(max(CumulativeSpend),[%MonthKey],[%DateKey]))

Thank you,

View solution in original post

7 Replies
swuehl
MVP
MVP

If the date is your only dimension, just remove the total qualifier from the Above() function.

jsaifuddin
Contributor II
Contributor II
Author

Hello Stefan,

Removing total doesn't make much difference. It still gives me accumulated sum from start of January till date.

Can you suggest some other solution?

Thank you.

Juzer

swuehl
MVP
MVP

Sorry, my mistake.

It should work with two dimensions and the removed TOTAL qualifier.

For example, use Monthname(Date) and Date as dimensions.

jsaifuddin
Contributor II
Contributor II
Author

Hi Stefan,

I am using a line chart. Can you provide an alternate solution?

Thank you.

swuehl
MVP
MVP

You can use a front-end approach using advanced aggregation, something like

=Sum(

Aggr(

rangesum(above(sum(Spend),0,NoOfDays))

, YearMonth, Date)

)

where YearMonth and Date are fields from your data model and Date needs to be created in chronological order or you need to sort the aggr() dimensions using The sortable Aggr function is finally here!

You can also create a script based solution using an AsOf table like shown here

The As-Of Table

Using a flag for date relations in the same month (in the blog example created simlar to the MonthDiff or YearDiff field), you can reset the accumulated value at Month border.

jsaifuddin
Contributor II
Contributor II
Author

Hi Stefan,

Thank you for alternate solutions.

I was able to solve it by implementing it in the load script and using sortable aggr on the front end to reset at the start of each month.

I implemented the following in my script:

if(peek(%MonthKey)=%MonthKey,rangesum(peek(CumulativeSpend),Spend),Spend) as CumulativeSpend

On front-end I used the following expression:

=sum(aggr(max(CumulativeSpend),[%MonthKey],[%DateKey]))

Thank you,

kdr_Qv
Contributor III
Contributor III

HI Swuehl,

 I have used your expression to sort out of my problem  to get last 2 months sum(sales) excluding current month for each date  (dimension value)..

(Sum(Aggr(rangesum(above(sum(Saleable),1)), [Report Date.autoCalendar.YearMonth]))
+ Sum(Aggr(rangesum(above(sum(Saleable),2)), [Report Date.autoCalendar.YearMonth])))
resulting value is correct..But this value representing only  for the first day of the month  (Feb 01, Mar 01, April 01) only.. Remaining   month dates  showing Zero (0).like below.. but i need to display last 2 months sales for entire current month(01 to 31st..)

kdr_Qv_1-1591149653987.png

in above table  i would like to get   same  above values for entire month   instead of '0's

march   all dates  ->  65,128   ,Feb  all dates-->13,754 .  apr -->123,444

can you please let me know how can i achieve this,

@swuehl @jsaifuddin @sunny_talwar