# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for
Did you mean:
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.

 AGREEMENTNO Sale Date Sale value 757 04/12/2018 600 757 04/12/2018 600 873 10/12/2018 310 900 14/12/2018 150

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]))

1 Solution

Accepted Solutions
MVP

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]))
7 Replies
MVP

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]))
Partner

What if u try:

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

MVP

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

 AGREEMENTNO Sale Date Sale value 757 04/12/2018 600 757 04/12/2018 600 873 10/12/2018 310 600 900 14/12/2018 150
Partner

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

MVP
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.
Partner

Can't argue with this !

Contributor III
Author

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