Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
tvisha
Creator II
Creator II

Set expression and Max Date, to display data for latest date

Hi,

I am trying to pull data for the latest date in a chart, if nothing is selected. I am using a set expression as 

=Sum({<[Week Starting Date]={"=Date($(Max([Week Starting Date])))"}>}[Unique Pageviews])

But the chart shows, 0 always,  What am I doing wrong. 

 

tvisha_0-1694528016667.png

 

And on second note,  In a similar chart, how can I put data for the last 4 weeks. So basically I need to display 2 sheets, one with latest week and one with last 4 weeks.

Thanks,

Rains

 

 

Labels (3)
2 Solutions

Accepted Solutions
Sohan_Patil
Contributor III
Contributor III

Hi,

I have loaded a sample sales data 

Sohan_Patil_0-1694529634038.png

Set Expression:

Last Week : Sum({<Week = {$(=max(Week))}>}Sales)

last 4 Weeks : Sum({<Week = {">$(=max(Week)-4)"}>}Sales)

View solution in original post

tvisha
Creator II
Creator II
Author

Unfortunately it is giving me a sum of all. Is it because I also have a dimension in addition?

View solution in original post

9 Replies
PrashantSangle

try

=Sum({<[Week Starting Date]={"=$(=Date(Max([Week Starting Date])))"}>}[Unique Pageviews])

 

Also check format of Week Starting Date & Date(Max([Week Starting Date])) in text box. If both are same then no issue, else use 2nd parameter of date() to set format

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
tvisha
Creator II
Creator II
Author

Thank you, this

=Sum({<[Week Starting Date]={"=$(=Date(Max([Week Starting Date])))"}>}[Unique Pageviews])

is showing me data but it is summing data for all, and not for latest date. 

tvisha
Creator II
Creator II
Author

Hi,

I even changed this too 

=Sum({<[Week Starting Date]={"=$(=Date(Max([Week Starting Date]),'DD/MM/YYYY'))"}>}[Unique Pageviews])

 

But still showing a total of all

Sohan_Patil
Contributor III
Contributor III

Hi,

I have loaded a sample sales data 

Sohan_Patil_0-1694529634038.png

Set Expression:

Last Week : Sum({<Week = {$(=max(Week))}>}Sales)

last 4 Weeks : Sum({<Week = {">$(=max(Week)-4)"}>}Sales)

tvisha
Creator II
Creator II
Author

Unfortunately it is giving me a sum of all. Is it because I also have a dimension in addition?

tvisha
Creator II
Creator II
Author

tvisha_0-1694530593331.png

 

 

tvisha_1-1694530623255.png

tvisha_2-1694530645065.png

 

 

 

Sohan_Patil
Contributor III
Contributor III

Hi,

After adding another dimension as customer segment 

Sohan_Patil_0-1694533367584.png

===================================================================================

Only customer segment as dimension,  I have four customer segments it will add the segment wise data as shown below example after adding the values of first bar (Corporate) of all the weeks then the total will be 539.21K. But the data is for last four weeks.

Sohan_Patil_1-1694533459129.png

 

PrashantSangle

1st try in straight table

add those dimension along with Week Starting Date field. Then add expression suggested by me & check it showing data for which date? If still not work for you then either share your working qvf or screen shot of expression editor. your chart that will help us.

Else we stuck in just guessing.

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
tvisha
Creator II
Creator II
Author

Hi,

Was able to pull the 1 week data, seems some syntax error. now struggling with 4 weeks, again looks like syntax error somewhere, as it is pulling 1 week for below but not 4 weeks

sum( { < [Week Starting] = {">$(=date(Max([Week Starting]) - 4))"} >} [Unique Pageviews] )