Cumulative of an aggregated value

[G3S]

hi,

There is a value (TEU) calculated using the following expression: 

sum(aggr(([CntrTEU]),[CntrSeal])).

I need the cumulative value of this value based on a selected date (ShippedTEU).  The modifier feature assists, however all dates up to the required date need to be selected. 

I want to be able to select a particular date and anything preceding that to be considered automatically. 

in the above table,  to get cumulative for 25/4/2020, I have to select all 4 dates. If I select only 25/4/2020, ShippedTEU returns 1396. 

What is the expression (even if set analysis) to return 5657 by selecting only 25/4/2020

thanks in advance.

[G3S]

have been trying variants of:

Sum({1<ETSWkEndDate={"=$(=GetCurrentSelection(ETSWkEndDate))"},ETSWkEndDate+={"<$(=CurrentSelction(ETSWkEndDate))"}>}aggr(CntrTEU,CntrSeal))

no luck still. 

[Kushal_Chawda]

create variable vMaxDate with below expression

=date(max(ETSWkEndDate))

Then you can below expression

=sum(aggr(rangesum(above(total sum({<ETSWkEndDate,ETSWkEndDate={"<=$(vMaxDate)"}>}CntrTEU),0,rowno())),ETSWkEndDate))*avg(1)

[sunny_talwar]

May be this

Sum({1<ETSWkEndDate = {"<=$(=Date(Max(ETSWkEndDate), 'DD/MM/YYYY'))"}>} Aggr(Only({1<ETSWkEndDate = {"<=$(=Date(Max(ETSWkEndDate), 'DD/MM/YYYY'))"}>} CntrTEU), CntrSeal))

But if you don't have repeating values,  you can try this

Sum({1<ETSWkEndDate = {"<=$(=Date(Max(ETSWkEndDate), 'DD/MM/YYYY'))"}>} CntrTEU)

[G3S]

thank you @sunny_talwar , the second expression works , but two issues. 

1. need to select multiple values. returns 0 if one value is selected for ETSWkEndDate.

2.  returns value  only upto the prev week.  it is not calculating for the max of selection. taking the sample data in my initial post & using the expression Sum({1<ETSWkEndDate = {"<=$(=Date(Max(ETSWkEndDate), 'D/M/YYYY'))"}>} Aggr( CntrTEU, CntrSeal))

selecting 25/4/2020 & 18/4/2020 returns 4260.  not the required 5657. 

[G3S]

@Kushal_Chawda thank you. script load ends in error when adding this line.

=date(max(ETSWkEndDate))

haven't used variables before.. 

[AbhijitBansode]

I understand from the example that, you are looking at cumulative total of the month on the selected date.

If you have a huge dataset, it would be better to have a AsOf Calendar table which can suffice this requirement:

As shown below, create AsOfCalendar table which links with all the dates you need to be linked with.

There are many ways using which you can achieve this in script.

Use columns highlighted in yellow as your filter where users can make selections:

Another cleaner way to achieve this is, assign unique IDs for each month in the script.

Then in set analysis:

Sum({<  MonthNo= {$(=Max(MonthNo))}, Date={"<= $(=Max(Date))"} >} measureColumn)

[Kushal_Chawda]

@G3S  You need to create the variable on frond end and not in load script.

create variable vMaxDate with below expression

=date(max(ETSWkEndDate))

Then try below expression

Sum({<ETSWkEndDate,ETSWkEndDate = {"<=$(vMaxDate)"}>} CntrTEU)

[sunny_talwar]

Would you be able to share a sample where we can see this issue?

[G3S]

@sunny_talwar sure

1. need to select multiple values. returns 0 if one value is selected for ETSWkEndDate.

2. returns value  only upto the prev week.

thank you