Distinct sum over rolling time period

[Ashwinyp]

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

Please help with the solution.

[sunny_talwar · Accepted solution]

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

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

[OmarBenSalem]

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

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

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