topic Re: Show 12 months when One month is selected in QlikView

Show 12 months when One month is selected

natisham — Tue, 18 Jul 2017 14:18:15 GMT

I have a chart trying to show the last 12 months beginning with the month I select. My current dimension is MonthYear.

My expression is sum({<MonthYear=>}[MACHINE HOURS])* avg({<MonthYear=>}[WORK PCT]/100)

I have it down to ignore the month year I select to show past months. But my chart keeps showing from Jun 2017 back to Feb 2016. How can I have the chart show all 12 months starting with my selected monthyear.

Re: Show 12 months when One month is selected

sunny_talwar — Tue, 18 Jul 2017 14:22:16 GMT

How do you create MonthYear field in the script? Assuming it is created like this

Date(MonthStart(DateField), 'MMM YYYY') as MonthYear

Try this

Sum({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -12), 'MMM YYYY') & '<=' & Date(Max(MonthYear), 'MMM YYYY'))"}, Year>} [MACHINE HOURS])* Avg({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -12), 'MMM YYYY') & '<=' & Date(Max(MonthYear), 'MMM YYYY'))"}, Year>} [WORK PCT]/100)

Re: Show 12 months when One month is selected

natisham — Tue, 18 Jul 2017 14:26:33 GMT

I get an error in expression.

Re: Show 12 months when One month is selected

sunny_talwar — Tue, 18 Jul 2017 14:28:29 GMT

And what is the error say

Re: Show 12 months when One month is selected

effinty2112 — Tue, 18 Jul 2017 14:28:42 GMT

Hi Natasha,

This is easy if you have a master calendar with a sequential numerical field for your months, not resetting at a new year. So if Dec 2016 had MonthID 67 say Jan 2017 would be 68.

If you have a MonthID like that then something like

sum({$<MonthID = {">='$(=-12+max(MonthID))'"}, MonthYear=>}[MACHINE HOURS]) might work for you.

There's bound to be a syntax error in there but I hope you get the gist.

cheers

Andrew

Re: Show 12 months when One month is selected

natisham — Tue, 18 Jul 2017 15:16:19 GMT

My monthyear in the calendar is date(monthstart(TempDate), 'MMM-YYYY') as MonthYear.

I updated expression to : Sum({<MonthYear = {"$(='>=' & date(AddMonths(Max(MonthYear), -12), 'MMM-YYYY') & '<=' & date(Max(MonthYear), 'MMM-YYYY'))"}, Year>} [MACHINE HOURS])* Avg({<MonthYear = {"$(='>=' & date(AddMonths(Max(MonthYear), -12), 'MMM-YYYY') & '<=' & date(Max(MonthYear), 'MMM-YYYY'))"}, Year>} [WORK PCT]/100)

when i change the MonthYear on my list box the data is changing for each month and getting incorrect data.

Re: Show 12 months when One month is selected

sunny_talwar — Tue, 18 Jul 2017 15:25:41 GMT

So based on your selection in MonthYear, wouldn't the number change because your are going to see 12 months from the selected date?? Is that not what you want? What are you hoping to see if Jan2017 is selected?

Re: Show 12 months when One month is selected

natisham — Tue, 18 Jul 2017 15:34:08 GMT

If Jan 2017 is selected then I would like to see Jan 2017 - Jan 2016. It is a bar graph, so each bar would represent a different month. So when i select Feb 2017. The bars represent feb 2017-feb 2016.

Re: Show 12 months when One month is selected

sunny_talwar — Tue, 18 Jul 2017 15:50:44 GMT

Okay so now we know what you want, can you tell me what the above expression is doing when you select Jan 2017? It is not showing Jan 2017 - Jan 2016? Showing another range of period?

Re: Show 12 months when One month is selected

natisham — Tue, 18 Jul 2017 16:45:42 GMT

When I click on Jan 2017 I see Feb 2016 to June 2017. If I click on Feb 2017, the same months Feb - June stay but the data changes. When i used expression :

sum({<MonthYear=>}[MACHINE HOURS])* avg({<MonthYear=>}[WORK PCT]/100)

I got the right data but still showed feb 2016 to June 2017 no matter what month i clicked on.

Re: Show 12 months when One month is selected

sunny_talwar — Tue, 18 Jul 2017 16:51:45 GMT

That is strange, would you be able to share a sample?

Re: Show 12 months when One month is selected

natisham — Tue, 18 Jul 2017 16:58:37 GMT

I cant share the qvd due to privacy but I have added some screenshots. I need to have my other graphs remain as is. So i cant touch the master calendar to much. In the bar graph I use dimension Monthyear.

Then My expression is :

And my Calendar:

Re: Show 12 months when One month is selected

sunny_talwar — Tue, 18 Jul 2017 17:39:37 GMT

Can you post a screenshot of this expression in a text box object?

='>=' & Date(AddMonths(Max(MonthYear), -12), 'MMM YYYY') & '<=' & Date(Max(MonthYear), 'MMM YYYY')

Re: Show 12 months when One month is selected

natisham — Tue, 18 Jul 2017 19:07:28 GMT

Re: Show 12 months when One month is selected

sunny_talwar — Tue, 18 Jul 2017 19:09:34 GMT

If this is what you see, I won't really know what you might be doing wrong because that proves to me that you should only see from May 2016 to May 2017 (assuming you have selected May 2017 or May 2017 is your max date.....

Re: Show 12 months when One month is selected

natisham — Tue, 18 Jul 2017 19:16:30 GMT

Can you help with writing it into my expression:

sum({<MonthYear=>}[MACHINE HOURS])* avg({<MonthYear=>}[WORK PCT]/100)

Re: Show 12 months when One month is selected

sunny_talwar — Tue, 18 Jul 2017 19:36:10 GMT

That is what I was trying to do .... But I don't really know how your data is set