Cumulative total or sum of the calculated field

Question & Answer
, ,
1

Hello QS Team,

I have created the following field

“periodToDateSumOverTime(sum(1500 -{value}),dt,MONTH)”

which essentially produce the data below

JAN £1,248
FEB £1,372
MAR £1,732
APR £86
MAY £1,977
JUN £889
JUL £1,469

My goal is to create another field which will display the running sum of the value above in another table. For instance:

sum of the calculated field: £8,773

Is there a way to achieve this please?

Best,

2

Can you wrap that in a sumOver?

sumOver(periodToDateSumOverTime(sum(1500 -{value}),dt,MONTH))

Or are you looking for a running sum?

runningSum(sum(1500 -{value}), [truncDate(“MM”,{Date}) ASC], [{qs_year}])

3

Hello Max, thanks for reverting back to me.

I have tried the sumOver function before and it did not generate the expected output (I got £495000 instead of £8,773).
I suspect that this is due to QS calculating 1500 - (value) on a daily basis using “dt” as reference (“dt” has over 1000 records) and then summing them all.

What I am looking for is for QS to perform the following at once :

  1. Summing all the value within a month to get “sub-total 1”,
  2. then minus the monthly “sub-total 1” from 1500 to get “sub-total 2” and
  3. finally adding up only “sub-total 2” to get a single total amount or runningSum

I also tried “runningSum(sum(1500 -{value}), [truncDate(“MM”,{Date}) ASC], [{qs_year}])” but encountered the following issue:

“{qs_year}” does not exist as a field -

image
image1321×126 5.82 KB

as a workaround, I created a calculated field parsing the Year portion of “dt” but doing so generated the wrong output again (I got £495000 instead of £8,773).

Any further assistance on this will be greatly appreciated.

best,

4

runningSum(sum(1500 -{value}), [truncDate(“MM”,{Date}) ASC], [truncDate(“YYYY”,{Date}) ])

Does that work?

In regards to summing over, have you tried partitioning by dt?

sumOver(periodToDateSumOverTime(sum(1500 -{value}),dt,MONTH),[dt],PRE_AGG)

5

Hello Max,

I am afraid not - I have tested the options you provided and none of them is working.

6

Ahh wait. Can you instead of grouping by the month, group by the year?

periodToDateSumOverTime(sum(1500 -{value}),dt,YEAR)