Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
G3S
Creator III
Creator III

Cumulative of an aggregated value

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. 

G3S_1-1598345467079.png

 

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.

 

 

 

Labels (4)
14 Replies
G3S
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

=date(max(ETSWkEndDate))

haven't used variables before.. 

AbhijitBansode
Specialist
Specialist

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:

AbhijitBansode_0-1599037700849.png

 

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
Creator III
Creator III
Author

@sunny_talwar sure

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

exmple1_1.JPG

example1_2.JPG

 

2. returns value  only upto the prev week.

example2.JPG

 

thank you