Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

year selection

If We select year, chart should display from previous month to preceeding 12 months.

I have written the below expression but I am getting issue, like If I select 2017 year it is taking from prev month upto Jan 2017.

But I need to display upto June 2016.

Can any one suggest me this?

6 Replies
Not applicable

Re: year selection

If(Getselectedcount(year)>0,

(if(NumOfFailures=0,

Sum({<EquipmentType={'GP-40MC'},CalendarMonth={'$(vPrevMonthYear)'}

,CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>}  AssetMiles)

/

Sum({<CalendarMonth={'$(vPrevMonthYear)'},EquipmentType={'GP-40MC'}

,CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} NumOfFailures),

Sum( {<EquipmentType={'GP-40MC'},CalendarMonth={'$(vMaxMonthYear)'},

CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} AssetMiles)

/

Sum({<EquipmentType={'GP-40MC'},CalendarMonth={'$(vMaxMonthYear)'},

CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} NumOfFailures)

))),

(if(NumOfFailures=0,

Sum({<EquipmentType={'GP-40MC'},CalendarMonth={'$(vPrevMonthYear)'}

,CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>}  AssetMiles)

/

Sum({<CalendarMonth={'$(vPrevMonthYear)'},EquipmentType={'GP-40MC'}

,CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} NumOfFailures),

Sum( {<EquipmentType={'GP-40MC'},CalendarMonth={'$(vMaxMonthYear)'},

CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} AssetMiles)

/

Sum({<EquipmentType={'GP-40MC'},CalendarMonth={'$(vMaxMonthYear)'},

CalendarMonth={'>=$(=Date(AddMonths(max(CalendarMonth),-12),'MMM-YY'))<$(=Date(AddMonths(max(CalendarMonth),0),'MMM-YY'))'}>} NumOfFailures)

))))

MVP
MVP

Re: year selection

What is your expression? Did you remember to include Year field in your set analysis so that you can  exclude the selection in the Year field (the field where you are making selection)?

MVP
MVP

Re: year selection

Why are you using set analysis on CalendarMonth field twice?

Capture.PNG

You know that the second one will not work, right? I don't see the reason to do this?

And what is NumOfFailures? Field? or Variable? Also, if it is a field, is this from a Island Table?

Not applicable

Re: year selection

One is for month but I tried for year I dint get any result to show the scenario I copied

My only intention, When I am selecting Year from that year, month should display from previous month to preceding 12 months and if I select month that is coming properly.

Numoffailures is field that is not coming from the isolated table. that field is coming from the fact table which is integrated from different transaction tables

MVP
MVP

Re: year selection

It might be easier to help if you can share a sample where you might be doing this... Would you be able to share a sample?

Not applicable

Re: year selection

Hi Sunny,

Thanks for your response, please find the below sample data

   

AssetMilesNumOfFailuresCalendarKey
35.14020150413
35.14020140319
35.56020150211
35.56020140823
43.6020140111
49.55020150529
62.8020140718
50.94020140629
50.94020140622
62.7020140204
70.28020141212
70.28020140328
70.28020140228
71.12020140727
82020140403
88.2020140911
99.1020141114
99.1020140509
56.93020150520
84.15020150528
87.14020140620
95.7020150310
101.9020150218
115.76020150316
123.34020140327
125.6020140410
130.8020150216
131.8020150209
104.6020150311
105.88020150401
106.4020150202
106.48020140502
114.53020150527
119.68020140131
121.2020140529
140.98020140905
141.02020141211
141.02020141210
141.44020140226
141.44020140123
142.24020140202
148.8020140322
158.9020140324
164020141022
164020141106
164020141016

I have one more doubt, we have filter Month(JAN,FEB.......), Year(2014,2015..).  If we select Aug value and year 2015 it should display from from July 2015 to Jun 2014. is it possible?