Display last 6 weeks in line chart based on selection.

[Prabhu]

Hi Team,

I need to display last six weeks from the selected week in line chart(WeekMonthYear as dimension).

I am presently using the following expression:

If(

Only(TOTAL {<WeekMonthYear = {">=$(=Max(WeekMonthYear) - 6)<=$(=Max(WeekMonthYear))"}>} [Field Category]) = 'Base (Required)' and

Only(TOTAL {<WeekMonthYear = {">=$(=Max(WeekMonthYear) - 6)<=$(=Max(WeekMonthYear))"}>} Model) = 'First Submissions',

Avg({<WeekMonthYear = {">=$(=Max(WeekMonthYear) - 6)<=$(=Max(WeekMonthYear))"}>} Aggr(Avg({<WeekMonthYear = {">=$(=Max(WeekMonthYear) - 6)<=$(=Max(WeekMonthYear))"}>} BASE_MODEL_A_QUALITY_SCORE), [File Type], WeekMonthYear)))

This expression is working for some selections and not working for some selections in WeekMonthYear field.

if i select 02-Mar-18 as week ending date,  week chart is not properly getting data i.e. last 6 week values.Please see the attached qvw.

Please do the needful.

@stalwar1

[sunny_talwar · Accepted solution]

Try this

If(

Only(TOTAL {<WeekMonthYear = {">=$(=date(WeekStart(max(WeekMonthYear), -5),'DD-MMM-YYYY'))<=$(=date(max(WeekMonthYear),'DD-MMM-YYYY'))"}, QuarterNew>} [Field Category]) = 'Base (Required)' and

Only(TOTAL {<WeekMonthYear = {">=$(=date(WeekStart(max(WeekMonthYear), -5),'DD-MMM-YYYY'))<=$(=date(max(WeekMonthYear),'DD-MMM-YYYY'))"}, QuarterNew>} Model) = 'First Submissions',

Avg({<WeekMonthYear = {">=$(=date(WeekStart(max(WeekMonthYear), -5),'DD-MMM-YYYY'))<=$(=date(max(WeekMonthYear),'DD-MMM-YYYY'))"}, QuarterNew>} Aggr(Avg({<WeekMonthYear = {">=$(=date(WeekStart(max(WeekMonthYear), -5),'DD-MMM-YYYY'))<=$(=date(max(WeekMonthYear),'DD-MMM-YYYY'))"}, QuarterNew>} BASE_MODEL_A_QUALITY_SCORE), [File Type], WeekMonthYear)))