topic Re: Display last 6 values in line chart based on selection. in QlikView

Display last 6 values in line chart based on selection.

Prabhu — Fri, 23 Mar 2018 16:35:47 GMT

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.

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Fri, 23 Mar 2018 16:40:20 GMT

Try this

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

Re: Display last 6 values in line chart based on selection.

Prabhu — Fri, 23 Mar 2018 17:33:22 GMT

Hi Sunny,

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

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Fri, 23 Mar 2018 17:35:43 GMT

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

Re: Display last 6 values in line chart based on selection.

Prabhu — Sun, 25 Mar 2018 09:49:01 GMT

Hi Sunny,

Please see the attached sample qvw.

@Sunny Talwar

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Sun, 25 Mar 2018 13:20:12 GMT

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)))

Re: Display last 6 values in line chart based on selection.

Prabhu — Sun, 25 Mar 2018 14:39:21 GMT

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

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Sun, 25 Mar 2018 15:08:45 GMT

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)))

Re: Display last 6 values in line chart based on selection.

Prabhu — Sun, 25 Mar 2018 15:26:45 GMT

Hi Sunny,

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

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Sun, 25 Mar 2018 15:32:38 GMT

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)))

Re: Display last 6 values in line chart based on selection.

Prabhu — Sun, 25 Mar 2018 15:56:32 GMT

Hi Sunny,

I tried this. But, it is taking the month values which are greater than selected month. We are supposed to get last six months from selected month.@stalwar1

Re: Display last 6 values in line chart based on selection.

Prabhu — Sun, 25 Mar 2018 16:31:41 GMT

Also, is there any way to make it work for last 6 Quarter values? @stalwar1

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Sun, 25 Mar 2018 23:56:26 GMT

I left an extra parenthesis in there... 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)))

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Sun, 25 Mar 2018 23:57:25 GMT

For last 6 quarter, may be go back 18 months rather than just 6 months

If(

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

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

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

Re: Display last 6 values in line chart based on selection.

Prabhu — Mon, 26 Mar 2018 04:53:38 GMT

Hi @stalwar1

MonthYear is working fine. But for Quarter, chart is displaying only one quater upon selecting the quarter value. It should display last 6 quarters from selected quarter. Please look into it.

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Mon, 26 Mar 2018 09:00:03 GMT

You really need to look into and learn set analysis my friend... try this

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 I hope there is nothing more you want to add here and if this works you would close this thread by marking correct and helpful responses above.

Best,

Sunny

Re: Display last 6 values in line chart based on selection.

Prabhu — Mon, 26 Mar 2018 09:10:44 GMT

Thank you very much sunny. i tried this. the chart is not taking Q1-2018. Except that it is working fine.

@stalwar1

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Mon, 26 Mar 2018 11:14:24 GMT

I am not sure what that means? I tried the above expression and it seems to be showing Q1-2018

Re: Display last 6 values in line chart based on selection.

Prabhu — Tue, 27 Mar 2018 05:27:30 GMT

Hi @Sunny Talwar

When we select Q1-2018 from Quarter list box, the chart should display Q1-2018, Q4-2017, Q3-2017, Q2-2017,Q1-2017 AND Q4-2016.

But, its not displaying Q1-2018.Please see the attached screenshot.

Re: Display last 6 values in line chart based on selection.

sunny_talwar — Tue, 27 Mar 2018 08:58:21 GMT

I am seeing it... I am not sure what you have... may be attach your qvw again