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: 
Ashwinyp
Contributor III
Contributor III

Distinct sum over rolling time period

Hi,

I have a dataset at application level. It also has AGREEMENTNO and the sale value and sale date with respect to that agreement. There are duplicates at AGREEMENTNO level. However, the sale value is the same for every AGREEMENTNO that is duplicated so there is no issue of which sale value has to be selected.

I have a date filter which uses the 'sale date'. I'm trying to create the cumulative sum of sales from the start of the month for distinct agreements for the 'sale date' that is selected.

AGREEMENTNOSale DateSale value
75704/12/2018600
75704/12/2018600
87310/12/2018310
90014/12/2018150

 

For example, if I select the 'Sale date' as 11/12/2018 from the Filter pane, it should show me the sum of sales as 910.

Currently, the below code is giving me only 600. (The AGREEMENTNO-873 is not being considered, I am unsure of the reason)

SUM({<
SALEDATE={">=$(=MONTHSTART(SALEDATE)) <=$(=MAX(SALEDATE))"}
>} Aggr(DISTINCT max([SALEVALUE]), [AGREEMENTNO]))

Please help with the solution.

1 Solution

Accepted Solutions
sunny_talwar

May be try adding the outer set analysis to the inner set analysis also....

Sum({<SALEDATE = {">=$(=MonthStart(SALEDATE))<=$(=Max(SALEDATE))"}>} Aggr(Max({<SALEDATE = {">=$(=MonthStart(SALEDATE))<=$(=Max(SALEDATE))"}>}[SALEVALUE]), [AGREEMENTNO]))

View solution in original post

7 Replies
sunny_talwar

May be try adding the outer set analysis to the inner set analysis also....

Sum({<SALEDATE = {">=$(=MonthStart(SALEDATE))<=$(=Max(SALEDATE))"}>} Aggr(Max({<SALEDATE = {">=$(=MonthStart(SALEDATE))<=$(=Max(SALEDATE))"}>}[SALEVALUE]), [AGREEMENTNO]))
OmarBenSalem

What if u try:

 

SUM( distinct {<SALEDATE={">=$(=MONTHSTART(SALEDATE)) <=$(=MAX(SALEDATE))"}>}  "Sale value")

sunny_talwar

This is very risky... what if the data was like this

AGREEMENTNOSale DateSale value
75704/12/2018600
75704/12/2018600
87310/12/2018310 600
90014/12/2018150
OmarBenSalem

Yeah, that could be a problem if u put in a KPI, but in a table, It won't be:

Capture.PNG

 

sunny_talwar

Well my point is that in general it is never a good idea to use DISTINCT with Sum unless you really are sure you understand what you are doing.
OmarBenSalem

Can't argue with this ! 

Ashwinyp
Contributor III
Contributor III
Author

@sunny_talwar This worked perfectly. Thanks! I wasn't aware that you should write the set analysis twice! But why?!