How do I force a chart to only show date for most recent 13 weeks?

[#1]

I need to create a chart that shows sales trends for only the most recent 13 weeks of data regardless of selections. I have tried enough different if statements and set analysis expressions to drive any man absolutely insane and cannot get it to work.

For simplicity's sake let's say I am just using 3 fields: InvoiceID, SaleAmt, SaleDate

Any help is greatly appreciated; I am losing my mind.

Thank you,

Stephan

[Gysbert_Wassenaar]

something like sum({<SaleDate={'>$(=today()-91)'}>}SaleAmt)

[#3]

Hi G Wassenaar,

I appreciate the quick response; I really need to figure this out before I go to bed.

The line you provided does not seem to work for me, and also I can't really use the today() function because there is lag in the data. It needs to be the 13 most recent weeks of the data which exists in the qvw, I should have specified that in my original post..

any ideas??

Thank you,

Stephan

[tresesco]

Similarly you can use Max(SaleDate) instead of today() and then subtract the number of days you want or so. If any doubt, please share your sample app.

[#5]

Also I should specify that this will be in a line chart, with sales rolling up to the last day of each week.

I am using WeekEnd(SaleDate) as the dimension

[#6]

Hi Tresesco,

I have tried every variation that I can think of using max(saledate), do you see anything else in the code G Wassenaar posted that could be causing it not to work properly?

Also I should specify that this will be in a line chart, with sales rolling up to the last day of each week.

I am using WeekEnd(SaleDate) as the dimension

Thanks,

Stephan

[tresesco]

Hi Stephan,

There could be couple of other issues like date-format, model. So please try to share your sample app to be helped in efficient way. If you are bothered about data security and all, have a look at how to prepare example for upload.

[#8]

So I ended up getting it to work with dimension limits...

I created another expression that was simply num(saledate), and limited to largest 13 (I used the num() function because otherwise the labels on the X axis were being shown as dates).

I ticked the box to make the expression invisible and removed all traces of it from the legend.

I then adjusted the scale to have a static min/max based on my second expression.

Is there any flaw in doing it this way? It seemed TOO easy after spending literally HOURS trying to write a set analysis expression to do the same thing....

If you think it's safer to use an expression rather than the method I used, I will revisit it tomorrow and provide sample data (the actual data I use cannot be shared) but for now I need to get to sleep.

Thanks again,

Stephan