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.

[Prabhu]

Hi @Sunny Talwar

Please see the quarter chart in the attached qvw.

[sunny_talwar]

You had dimension limits on your chart....  remove that and it should work with this expression

If(

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

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

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

Now if you don't mind, please close this thread by marking correct and helpful responses

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[Prabhu]

Hi stalwar1,

When i remove dimension limits, chart is displaying 7 Quarters. It should display 6 quarters only.

[sunny_talwar]

prabhakar.vammi‌ - Are you unable to change the date range condition on the set analysis ?

[sunny_talwar]

Try this

If(

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

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

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

[Prabhu]

Thanks sunny. Quarter chart is working fine now.

Could you please look into the Week End chart. 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. I will definitely mark  them as correct.

@Sunny Talwar

[sunny_talwar]

I would suggest you to open another thread as we have already done a lot of different things in this thread. You can tag me and I am more than happy to help you