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

[tvisha]

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. 

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

[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

[tvisha]

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]

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 · Accepted solution]

Hi,

I have loaded a sample sales data 

Set Expression:

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

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

[tvisha · Accepted solution]

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

[tvisha]

[Sohan_Patil]

Hi,

After adding another dimension as customer segment 

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

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.

[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

[tvisha]

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] )