Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have fields named as Month 1, Month 2, Month 3, Month 4, Month 5.
Where Month 1 is current month and month 2 till month 5 are next months.
What i want is when i select Dec 2017 from filter pane, my month fields name should change according to selection in chart.
Example data
I select Dec 2017 the month fields name changed from Month 1 to Dec-17, Month 2 to Jan-18, Month 3 to Feb-18 and so on.
And when Jan 2018 is selected from filter then the months fields name should changed as per below snap.
Want this in bar chart
Is this possible? If yes then please help
Thanks
Hi,
Assuming that when no selection or when more than 1 month selected then Month 1 = Current Month
Label for Month 1:
=if(GetSelectedCount(YearMonth)=1,GetFieldSelections(YearMonth),Month(Today())&Num(Year(Today())-2000,'-00'))
Label for Month 2:
=if(GetSelectedCount(YearMonth)=1,Pick(Match(Left(GetFieldSelections(YearMonth),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan')
&'-'&
If(Match(Left(GetFieldSelections(YearMonth),3),'Dec'),Num(Right(GetFieldSelections(YearMonth),2)+1,'00'),Right(GetFieldSelections(YearMonth),2)),Month(addmonths(Today(),1))&Num(Year(addmonths(Today(),1))-2000,'-00'))
Label for Month 3:
=if(GetSelectedCount(YearMonth)=1,Pick(Match(Left(GetFieldSelections(YearMonth),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb')&
'-'&If(Match(Left(GetFieldSelections(YearMonth),3),'Nov','Dec'),Num(Right(GetFieldSelections(YearMonth),2)+1,'00'),Right(GetFieldSelections(YearMonth),2))
,Month(addmonths(Today(),2))&Num(Year(addmonths(Today(),2))-2000,'-00'))
Label for Month 4:
=if(GetSelectedCount(YearMonth)=1,Pick(Match(Left(GetFieldSelections(YearMonth),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar')&
'-'&If(Match(Left(GetFieldSelections(YearMonth),3),'Oct','Nov','Dec'),Num(Right(GetFieldSelections(YearMonth),2)+1,'00'),Right(GetFieldSelections(YearMonth),2))
,Month(addmonths(Today(),3))&Num(Year(addmonths(Today(),3))-2000,'-00'))
Label for Month 5:
=if(GetSelectedCount(YearMonth)=1,Pick(Match(Left(GetFieldSelections(YearMonth),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr')&
'-'&If(Match(Left(GetFieldSelections(YearMonth),3),'Sep','Oct','Nov','Dec'),Num(Right(GetFieldSelections(YearMonth),2)+1,'00'),Right(GetFieldSelections(YearMonth),2))
,Month(addmonths(Today(),4))&Num(Year(addmonths(Today(),4))-2000,'-00'))
Few examples:
I hoe this helps,
Cheers,
Luis
Hi,
You should use set analysis Month between Max(Month) and Max(Month) + 5
Something like this :
Sum({< Month=,
Date= {">=$(=MonthStart(Max(Month))) <=$(=Addmonths(Max(Month),5))"} >} Value)
Hope this helps
Justin.
I only want that fields name should change according to selection.
Hi,
Why don't you use cross table to convert fields name Month 1, Month 2, Month 3, .... to one Column Month
You can refer Cross Table: Working with crosstables in the data load script ‒ Qlik Sense
Thanks,
Justin.
Hi,
Assuming that when no selection or when more than 1 month selected then Month 1 = Current Month
Label for Month 1:
=if(GetSelectedCount(YearMonth)=1,GetFieldSelections(YearMonth),Month(Today())&Num(Year(Today())-2000,'-00'))
Label for Month 2:
=if(GetSelectedCount(YearMonth)=1,Pick(Match(Left(GetFieldSelections(YearMonth),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan')
&'-'&
If(Match(Left(GetFieldSelections(YearMonth),3),'Dec'),Num(Right(GetFieldSelections(YearMonth),2)+1,'00'),Right(GetFieldSelections(YearMonth),2)),Month(addmonths(Today(),1))&Num(Year(addmonths(Today(),1))-2000,'-00'))
Label for Month 3:
=if(GetSelectedCount(YearMonth)=1,Pick(Match(Left(GetFieldSelections(YearMonth),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb')&
'-'&If(Match(Left(GetFieldSelections(YearMonth),3),'Nov','Dec'),Num(Right(GetFieldSelections(YearMonth),2)+1,'00'),Right(GetFieldSelections(YearMonth),2))
,Month(addmonths(Today(),2))&Num(Year(addmonths(Today(),2))-2000,'-00'))
Label for Month 4:
=if(GetSelectedCount(YearMonth)=1,Pick(Match(Left(GetFieldSelections(YearMonth),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar')&
'-'&If(Match(Left(GetFieldSelections(YearMonth),3),'Oct','Nov','Dec'),Num(Right(GetFieldSelections(YearMonth),2)+1,'00'),Right(GetFieldSelections(YearMonth),2))
,Month(addmonths(Today(),3))&Num(Year(addmonths(Today(),3))-2000,'-00'))
Label for Month 5:
=if(GetSelectedCount(YearMonth)=1,Pick(Match(Left(GetFieldSelections(YearMonth),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr')&
'-'&If(Match(Left(GetFieldSelections(YearMonth),3),'Sep','Oct','Nov','Dec'),Num(Right(GetFieldSelections(YearMonth),2)+1,'00'),Right(GetFieldSelections(YearMonth),2))
,Month(addmonths(Today(),4))&Num(Year(addmonths(Today(),4))-2000,'-00'))
Few examples:
I hoe this helps,
Cheers,
Luis
Can you please share the sample file as it seems really helpful
Attached,
I hope this is what you're after,
Regards,
Luis
Sorry but can't find the attachment
would be thankful if u guide
thanks
Strange that you can't see the attachment.
Basically one table with Month 1, Month 2, etc.
Another table with the YearMonth you'd like to use to select in format Month-Year
Then in the application, a filter pane with the YearMonth .for Selection
then a Table with Dimension 1 = Month 1 and the label as shown in previous post. Dimension 2 = Month 2 and label as shown in previous post, and so on
I hope this helps,
Cheers,
Luis