Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
How one can display the dynamic column as per date criteria. Scenario has been enclosed for all of your reference. Help will be immensely appreciated.
Thanks, have a great time!!!
Hi,
Check this,
Based on the date Selection, It will reflect the previous months of the current year i.e the selected date Year.
In First instance Max(Date) Means, 12th Month -Dec, look into below
In Second Instance ,I have selected 2nd Month - Feb,Look into below
By doing this and some conditions based on selections,I hope it achieved as you required,
&
Therefore using value list in dimension, & These are all the expressions for your information,
=If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt',
Sum({<Date={">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"} ,Year={'$(=Year(Max(Date)))'}>}Amt),
If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt1',
Sum({<Date={">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"} ,Year={'$(=Year(Max(Date)))'}>}Amt1),
If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt2',
Sum({<Date={">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"} ,Year={'$(=Year(Max(Date)))'}>}Amt2))))
=If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt',
Sum({<Date={">=$(=MonthStart(Max(Date),-1))<=$(=AddMonths(Max(Date),-1))"} ,Year={'$(=Year(Max(Date)))'}>}Amt),
If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt1',
Sum({<Date={">=$(=MonthStart(Max(Date),-1))<=$(=AddMonths(Max(Date),-1))"} ,Year={'$(=Year(Max(Date)))'}>}Amt1),
If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt2',
Sum({<Date={">=$(=MonthStart(Max(Date),-1))<=$(=AddMonths(Max(Date),-1))"} ,Year={'$(=Year(Max(Date)))'}>}Amt2))))
Follows all the consecutive expressions and based on Expression Conditional.
=If(Year(MonthStart(Date(Max(Date),'DD-MM-YY'),-1))=Year(Max(Date)),'1','0')
They will appear.
Hope this Helps,
PFA,
Hirish
please post sample data in excel or sample app
There is no such option to attach the sample data right here in this conversation. Is there any other way so that i share my sample data.
Else i need to post the details again and qlik community approve it and so on ....lots of formality ......
Thanks a ton for you respond.
Use Advanced Editor on your reply and on the bottom of the editor you will see Attach option
Hi Vineeth,
In my case and for this current conversation the "use advance editor" option is not there? I did multiple r&d works still it's not coming. While I googled for the same issue, I found some people were had same issue and I clicked the replay button over there and the "use advance editor" option was there. Didn't get the logic,
why it behave like this..
Anyway thanks for your kind support and precious time. If possible please let me know how i could post the sample data.
have a great time!!!
Please disable your'e add blocker on browser and try. Or use another browser.
-Hirish
Hi Hirish,
Thanks for your valuable time, but the setting suggested by is already there. for this conversation it is allowing me the "advance editor " option.
so here am enclosing my sample data, don't know Vineeth will able to see the enclosed sample data file or not.
Hi,
Check this,
Based on the date Selection, It will reflect the previous months of the current year i.e the selected date Year.
In First instance Max(Date) Means, 12th Month -Dec, look into below
In Second Instance ,I have selected 2nd Month - Feb,Look into below
By doing this and some conditions based on selections,I hope it achieved as you required,
&
Therefore using value list in dimension, & These are all the expressions for your information,
=If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt',
Sum({<Date={">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"} ,Year={'$(=Year(Max(Date)))'}>}Amt),
If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt1',
Sum({<Date={">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"} ,Year={'$(=Year(Max(Date)))'}>}Amt1),
If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt2',
Sum({<Date={">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"} ,Year={'$(=Year(Max(Date)))'}>}Amt2))))
=If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt',
Sum({<Date={">=$(=MonthStart(Max(Date),-1))<=$(=AddMonths(Max(Date),-1))"} ,Year={'$(=Year(Max(Date)))'}>}Amt),
If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt1',
Sum({<Date={">=$(=MonthStart(Max(Date),-1))<=$(=AddMonths(Max(Date),-1))"} ,Year={'$(=Year(Max(Date)))'}>}Amt1),
If(ValueList('Expression of Amt','Expression of Amt1','Expression of Amt2')='Expression of Amt2',
Sum({<Date={">=$(=MonthStart(Max(Date),-1))<=$(=AddMonths(Max(Date),-1))"} ,Year={'$(=Year(Max(Date)))'}>}Amt2))))
Follows all the consecutive expressions and based on Expression Conditional.
=If(Year(MonthStart(Date(Max(Date),'DD-MM-YY'),-1))=Year(Max(Date)),'1','0')
They will appear.
Hope this Helps,
PFA,
Hirish
My heartfelt gratitude to you ......I need to implement the logic in my real time scenario.
Hi,
Is there any dynamic way if i have above 100 expressions ....