How to show 5 recent weeks of data in bar chart

[ashis]

I want to create a bar chart that would show me recent last 5 weeks data by default. And then when user selects any date
then based on the selection it would show that week and previous 4 weeks 0f data in the same fashion.
So in my load script , i have this code for dates.

DATE,
WeekDay(DATE) as D_WEEKDAY,
Year (DATE) as D_YEAR,
Month (DATE) as D_MONTH,
Year(DATE) * 12 + num(Month(DATE)) as [MonthCounter],
Date(MonthStart(DATE),'MMM-YYYY') as MonthYear,
Day (DATE) as D_Day,
week(DATE) as D_Week,
AutoNumber(Year(DATE) & Week(DATE)) AS WeekSerial

Then in the chart expression i am using the following code to show 5 recent weeks,

=FABS(sum({<WeekSerial={"<=$(=(max(WeekSerial)))>=$(=(max(WeekSerial)-4))"},D_MONTH,D_YEAR>} Sales))

The above approach is not working when i click on the date filer, then it is only showing me that week data .
I need to show that week and 4 previous weeks as well (total 5 weeks).

Please advise.

[Anonymous]

you might deselect other calendar fields as well in set analysis

e.g. if you select a week in your calendar, you might use
=FABS(sum({<D_Week=,WeekSerial={"<=$(=(max(WeekSerial)))>=$(=(max(WeekSerial)-4))"},D_MONTH,D_YEAR>} Sales))
If you select a day in your calendar, i would use

=FABS(sum({<D_Day=,D_Week=,WeekSerial={"<=$(=(max(WeekSerial)))>=$(=(max(WeekSerial)-4))"},D_MONTH,D_YEAR>} Sales))

The final result depend on your dimensions, expressions and selected fields

[ashis]

Thank you for your reply, I tried your solution , it did  not work.

[Anonymous]

can you post example qvw?

[ashis]

I am afraid to share the application file due to policy.

[ashis]

Hi, I tried the following code in expression , problem is it is giving me all the weeks data instead of 5 weeks.

=fabs(Sum({<DATE={">=$(=num(WeekStart(Max(DATE), -5)),'DD-MM-YYYY')<=$(=num(WeekEnd(Max(DATE))),'DD-MM-YYYY')"},D_MONTH=,D_YEAR=,D_Week=,D_Day=>} sales))

As of now , from dimension limit i restrict it first 5 values to get 5 weeks data.

Please let me know where my code is wrong.

Thank you.

[Anonymous · Accepted solution]

you are using the data format function "DD-MM-YYYY' without the command date 

either use =$(=date(num(WeekStart(Max(date('2019-05-01')), -5)),'DD-MM-YYYY'))

Maybe you donot need the funtion at all (if it is the same format), the drop it to $(=num(WeekStart(Max(DATE), -5)))

your expression_ $(=num(WeekStart(Max(DATE), -5)),'DD-MM-YYYY')

similiar your <= condition

[ashis]

Thank you Hrlinder for your advise. I did as per your suggestion and it worked. I appreciate your time.

Thank you.

[YairSharon]

simple solution:

go to dimension and add 'Weekserial' as  dimension.

add the 'WeekSerial' as first Chart expression.

in display option don't use any  selection.

add the aggregation expression.

in dimension limits mark show only....largest 5

remember to sort the 'WeekSerial' descending