Display last 6 values in line chart based on selection.

[Prabhu]

Hi Team,

I need to display last six years from the selected year in line chart(year as dimension).

It is working fine with the following expression:

avg({<  Year = {"<=$(=max(Year))>=$(=max(Year) - 6)"} >} QUALITY_SCORE)

But, as per the requirement, i need the following expression:

If([Field Category]='Base (Required)' and Model='First Submissions', avg(aggr(avg({<Year = {"<=$(=max(Year))>=$(=max(Year) - 6)"}  >} QUALITY_SCORE), [File Type],Year)))

if i use this expression, it is showing only selected year in line chart but not last six years.

Please do the needful.

[sunny_talwar]

Try this

Avg({<Year = {"<=$(=max(Year))>=$(=max(Year) - 6)"}>}Aggr(Avg({<Year = {"<=$(=max(Year))>=$(=max(Year) - 6)"}>} QUALITY_SCORE), [File Type], Year)))

[Prabhu]

Hi Sunny,

I tried that but no luck. It still shows only selected value in the dimension.

[sunny_talwar]

Would you be able to share a sample where this isn't working?

[Prabhu]

Hi Sunny,

Please see the attached sample qvw.

@Sunny Talwar

[sunny_talwar · Accepted solution]

Try this

If(

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

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

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

[Prabhu]

Thank you very much sunny. It is working well for Year.

But, challenge is it is not working for Month,Quarter, and Week as dimension.

I tried it but no luck. Please see the new attachment. Your help is appreciated.

@Sunny Talwar

[sunny_talwar]

Your Monthyear field is a text field... create it using Date() function like this

Date(MonthStart(CALENDAR_DT), 'MMM-YYYY') as MonthYear,

and then try this

If(

Only(TOTAL {<MonthYear = {">=$(=date(max(MonthYear),'MMM-YYYY')) - 6)<=$(=date(max(MonthYear),'MMM-YYYY')))"}>} [Field Category]) = 'Base (Required)' and

Only(TOTAL {<MonthYear = {">=$(=date(max(MonthYear),'MMM-YYYY')) - 6)<=$(=date(max(MonthYear),'MMM-YYYY')))"}>} Model) = 'First Submissions',

Avg({<MonthYear = {">=$(=date(max(MonthYear),'MMM-YYYY')) - 6)<=$(=date(max(MonthYear),'MMM-YYYY')))"}>} Aggr(Avg({<MonthYear = {">=$(=date(max(MonthYear),'MMM-YYYY')) - 6)<=$(=date(max(MonthYear),'MMM-YYYY')))"}>} BASE_MODEL_A_QUALITY_SCORE), [File Type], MonthYear)))

[Prabhu]

Hi Sunny,

I have tried this. But no luck. Please see the attached qvw.@stalwar1

[sunny_talwar]

Trying to go back 6 months? Try this

If(

Only(TOTAL {<MonthYear = {">=$(=date(MonthStart(max(MonthYear), -6),'MMM-YYYY'))<=$(=date(max(MonthYear),'MMM-YYYY')))"}>} [Field Category]) = 'Base (Required)' and

Only(TOTAL {<MonthYear = {">=$(=date(MonthStart(max(MonthYear), -6),'MMM-YYYY'))<=$(=date(max(MonthYear),'MMM-YYYY')))"}>} Model) = 'First Submissions',

Avg({<MonthYear = {">=$(=date(MonthStart(max(MonthYear), -6),'MMM-YYYY'))<=$(=date(max(MonthYear),'MMM-YYYY')))"}>} Aggr(Avg({<MonthYear = {">=$(=date(MonthStart(max(MonthYear), -6),'MMM-YYYY'))<=$(=date(max(MonthYear),'MMM-YYYY')))"}>} BASE_MODEL_A_QUALITY_SCORE), [File Type], MonthYear)))