Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
SAM81
Contributor
Contributor

SUM value for last date in that week

I have a table that contains both week and date values.  This table contains a measure called CREATED

I have a chart which trends the weekly CREATED volume, but i need this to only show the CREATED value for the last date within that week.

The data is as per the below example.  Instead of showing the sum total value for wc15th, which would be 147, i would like it to only show the value for the last day within that week, so 44.  For wc22nd, it should show 12

Any ideas?

 

SAM81_0-1617201395346.png

 

3 Replies
shayd
Contributor III
Contributor III

Hi,

I'm not familiar with your data but if the table in the image shows the original script table
without any special manipulations maybe you can try to create a group by table on your
original table using script:

LOAD
          WEEK_COMMENCING,

          MAX(DATE)

          CREATED
From {your table name} 
Group By WEEK_COMMENCING, CREATED;


Good luck!

 

 

 

SAM81
Contributor
Contributor
Author

Thanks for the suggestion, but I couldn't quite get that to work as I needed.  I have found the following solueiton on a different thread:

max(aggr(sum(aggr(sum(CREATED),WEEK_COMMENCING,DATE)),DATE))

This almost works perfectly, but returns the max value for that week, not the max value for the max date in that week.  

 

Any ideas on how I can amend?

 

Quy_Nguyen
Specialist
Specialist

Hello,

Try this expression : FirstSortedValue(CREATED,-DATE)

Tested result:

Quy_Nguyen_0-1617795524174.png