Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SAM81
Contributor II
Contributor II

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 II
Contributor II
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