How to get only latest 10 weeks as a dimension in a bar chart

[hbandari3636]

Hi all,

I have a field coming from database which has only Friday dates in that example 10/02/2015,09/25/2015,09/18/2015 etc upto 2014. I need to create a bar chart where the dimension is the above week ending date field and expression is if(xy=%,avg(values),sum(values)). I need to get only latest 10 weeks to be displayed on the chart starting from today. the dimensional limits tab of chart is only getting the highest values based on the Values field. Do we have a option of setting range in the dimension of the chart.

Thanks in advance.

[#2]

you would want to use set analyis in your expression where you would calcualted back 10 weeks from today

the dimesion would be your date field and the set analysis would be limiting the results of your expression to just the 10 weeks

[hbandari3636]

Can you give a correct example please of what do you mean by limiting the result of expression.....?

[JonnyPoole]

If you just want the data set to be the last 10 weeks of data from the latest date (today or your max date in the model):

Sum (   {<Date={">=$(=Date(adddays(Today(),-70)))<=$(=Date(Today()))"}>}   Sales)

or

use a variable:     vCurrentDate:     =vMaxDate

Sum(   {<Date={">=$(=Date(vMaxDate))<=$(=Date(Today()))"}>}   Sales)

And... ensure your load script is wrapping your date field with Date(). IE:     Date(Date)  as Date

[JonnyPoole]

realize i had a typo for 2nd option;

use a variable:     vCurrentDate:     =max(Date)

Sum(   {<Date={">=$(=Date(vMaxDate))<=$(=Date(Today()))"}>}   Sales)