Accumulate amount by month per day - Qlik Community

Report Inappropriate Content · ‎2016-02-08

Hello Community,

First time here...

Please be so kind as to point me in the right direction with regards to the following:

We have just installed Qlik Sense on our servers. (going for 2nd week)

I am trying to display a chart in Qlik Sense so that it displays the same or similar way that it is currently displaying on my excel spreadsheet below: (Accumulated amount per month per day)

I have tried the below suggestions found on several forums, but none of these worked.

// rangesum( above( sum(AmtCollected),0,RowNo()))

// sum( aggr( rangesum( above( sum(AmtCollected),0,12) ),Month))

// RangeSum(Above(Total sum(AmtCollected),0,RowNo()))

// sum(AmtCollected)

The fields that I am currently using are as follow:

Any idea on what I am doing wrong?

My result does not accumulate the values per day. If I remove a dimension day, i.e to display per month, it does accumulate per month.

My result per day:

Thank you in advance,

Mornay

brunobertels · ‎2016-02-08

Hi Mornay

Try this :

rangesum(

aggr(

rangesum(above(sum(AmtCollected)),0,RowNo()))

,Month,Day))

I test it with my own data it gave me this :

Regards

reddy-s · ‎2016-02-08

Hi Mornay,

You can use this to replicate the multi-line line chart above:

Please find the sample qvf attached.

Thanks,

Sangram.

Report Inappropriate Content · ‎2016-02-08

Hi Bruno, thank you for you response. I have tried your expression, but I am receiving this error... Error in expression: Aggr takes at least 2 parameters

This is what I have tried:

and

Your chart is exactly what I need, but I am unable to replicate it. Could you perhaps attach the file for me?

I have manage to add the expression, but now it only shows zero's...

rangesum( aggr( rangesum(below( sum(AmtCollected), 0, rowno() )), Day, MONTH ))

Thank you kindly

sunny_talwar · ‎2016-02-08

I think you will need to create Month and Day in your script so that you can use them in Aggr() function:

LOAD DB_Date,

Month(DB_Date) as MONTH,

Day(DB_Date) as Day

FROM ...

and then try this as your expression:

RangeSum(Aggr(RangeSum(Below(Sum(AmtCollected), 0, RowNo() )), Day, MONTH))

Another important this to ensure here to to sort the DB_Date correctly in order to get correct accumulation here. If the date isn't sorted in the script, you might run into errors.

Report Inappropriate Content · ‎2016-02-08

Hi Sunny,

Unfortunately I did not come right with the accumulation. I have added the statement to my select statement, but It does not accumulate....

Thank you

jonathandienst · ‎2016-02-08

I think the problem is that Above() and Below() work differently when you have multiple dimensions. You may need to use Above(TOTAL ...) in place of Above(...) and Below(TOTAL ...) in place of Below(..).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Report Inappropriate Content · ‎2016-02-08

Hi Jonathan, nope still nothing

sunny_talwar · ‎2016-02-08

May be add TOTAL to RowNo() as well -> RowNo(TOTAL)

brunobertels · ‎2016-02-08

Hi

The wright order is Month,Day your wrote in your expression Day, Month

here is the good one :