Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to display dynamic column as per date criteria ?

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

1 Solution

Accepted Solutions
HirisH_V7
Master
Master

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

Based on selection - Previous months data & till date-244381.PNG

In Second Instance ,I have selected  2nd Month - Feb,Look into below

Based on selection - Previous months data & till date 2 -244381.PNG

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

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

24 Replies
vinieme12
Champion III
Champion III

please post sample data in excel or sample app

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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.

vinieme12
Champion III
Champion III

Use Advanced Editor on your reply and on the bottom of the editor you will see Attach option

how_to_attach.JPG

2how_to_attach.JPG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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

HirisH_V7
Master
Master

Please disable your'e add blocker on browser and try. Or use another browser.

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
Not applicable
Author

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.

HirisH_V7
Master
Master

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

Based on selection - Previous months data & till date-244381.PNG

In Second Instance ,I have selected  2nd Month - Feb,Look into below

Based on selection - Previous months data & till date 2 -244381.PNG

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

HirisH
“Aspire to Inspire before we Expire!”
Not applicable
Author

My heartfelt gratitude to you ......I need to implement the logic in my real time scenario.

Not applicable
Author

Hi,

Is there any dynamic way if i have above 100 expressions ....