Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
ugurarslan
Contributor

cumulative table with sum cash applied versus total invoiced$ per month

Dear all,

I have table with the following dimension and measurement:

Dimension:

day([Month date])

Measurement:

(Sum({<[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

Table now shows:

DaysMeasurement
16
23
35
46
58
68
74
83
96
107
118
128
138
148
159
169
176
184
193
202

Now I want to see this result as cumulative.

DayCumulative
16
29
314
420
528
636
740
843
949
1056
1164
1272
1380
1488
1597
16106
17112
18116
19119
20121

How do I need to do this?

Please help me.

Ugur

Tags (3)
9 Replies
Partner
Partner

Re: cumulative table with sum cash applied versus total invoiced$ per month

from:

sum(YourMeasure)

do :

aggr(

RangeSum(above(

Sum({<Days>}Measurement)

,0,RowNo()))

,(Days,ascend))


Result:

Capture.PNG

Even iuf u select a day, u'll have the cumulative sum well calculated:

Capture.PNG

and with the aggr() part, even if ur table is not sorted by ascending days, it'll still be calculated well:

Capture.PNG

ugurarslan
Contributor

Re: cumulative table with sum cash applied versus total invoiced$ per month

aggr(

RangeSum(above(

Sum({<Days>}

[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD])

)

,0,RowNo()))

,(Days,ascend))

This didn't work...

ugurarslan
Contributor

Re: cumulative table with sum cash applied versus total invoiced$ per month

aggr(

RangeSum(

above(

Sum({<[Month date]>}

[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD])

)

,0

,RowNo()))

,([Month date],ascend))

ugurarslan
Contributor

Re: cumulative table with sum cash applied versus total invoiced$ per month

didn't work also

Partner
Partner

Re: cumulative table with sum cash applied versus total invoiced$ per month

aggr(

RangeSum(above(

(Sum({<Days, [Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<Days,[Year Month]={'$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

,0,RowNo()))

,(Days,ascend)

ugurarslan
Contributor

Re: cumulative table with sum cash applied versus total invoiced$ per month

Hi Omar, I couldn't define days. the days field is extracted from a formula to extract out of a day with using day([Month Date]). Can you please sent me the printscreens of the formula you put in your qliksense file.

Thanks

Ugur

ugurarslan
Contributor

Re: cumulative table with sum cash applied versus total invoiced$ per month

I appreciate your help very very much, thanks for learning me.

ugurarslan
Contributor

Re: cumulative table with sum cash applied versus total invoiced$ per month

Omar are you able to help me please?

Partner
Partner

Re: cumulative table with sum cash applied versus total invoiced$ per month

Can't u create the days dimension in the script then us it?