topic Re: How to display dynamic column as per date criteria ? in QlikView

How to display dynamic column as per date criteria ?

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to display dynamic column as per date criteria ?

vinieme12 — Sat, 24 Dec 2016 07:31:52 GMT

please post sample data in excel or sample app

Re: How to display dynamic column as per date criteria ?

Sat, 24 Dec 2016 12:41:55 GMT

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.

Re: How to display dynamic column as per date criteria ?

vinieme12 — Sun, 25 Dec 2016 07:22:05 GMT

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

Re: How to display dynamic column as per date criteria ?

Mon, 26 Dec 2016 05:47:12 GMT

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

Re: How to display dynamic column as per date criteria ?

HirisH_V7 — Mon, 26 Dec 2016 06:00:03 GMT

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

-Hirish

Re: How to display dynamic column as per date criteria ?

Mon, 26 Dec 2016 06:27:43 GMT

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.

Re: How to display dynamic column as per date criteria ?

HirisH_V7 — Mon, 26 Dec 2016 08:39:19 GMT

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

Re: How to display dynamic column as per date criteria ?

Mon, 26 Dec 2016 09:46:13 GMT

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

Re: How to display dynamic column as per date criteria ?

Mon, 26 Dec 2016 10:41:50 GMT

Hi,

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

Re: How to display dynamic column as per date criteria ?

HirisH_V7 — Mon, 26 Dec 2016 11:07:37 GMT

AFAIK no way,But some changes in the Requirement i.e by making the loops in the Script itself and creating required columns dynamically and using them.

I believe we need to do some R&D for this model.

Re: How to display dynamic column as per date criteria ?

vinieme12 — Mon, 26 Dec 2016 11:24:53 GMT

check the attached

Re: How to display dynamic column as per date criteria ?

Mon, 26 Dec 2016 11:55:53 GMT

Enclosed qvw is the right one posted by hirishv7, but is there any dynamic way if i have above 100 expressions ....

how to including the loop concept in it.

Thanks Vineeth for your valuable time.

Re: How to display dynamic column as per date criteria ?

vinieme12 — Mon, 26 Dec 2016 12:26:29 GMT

what I attached was a dynamic one, have you checked??

Re: How to display dynamic column as per date criteria ?

Mon, 26 Dec 2016 13:25:33 GMT

so sorry didn't notice ......It's a dynamic one,will chk and get back to you ASAP

Re: How to display dynamic column as per date criteria ?

vinieme12 — Wed, 28 Dec 2016 15:44:30 GMT

Hi stalwar1‌ , swuehl‌ , gwassenaar‌

I've been trying to go get this to work but couldn't.

On the below table what we need is if we select 19th April 2013

"Selected Column" to show data for selected date ie 19th April 2013 only

"Trailing": Should show data from Jan(1-19),Feb(1-19).....to April(1-19)

At the moment Date Selection is only calculating for selected Month , even though I've specified criteria in Set to evaluate from Month 1 to Selectedmonth

Thanks

Vineeth

Re: How to display dynamic column as per date criteria ?

sunny_talwar — Wed, 28 Dec 2016 15:50:06 GMT

vinieme12‌‌ are you looking for this?

Expressions

=if(Only({1}PeriodRange) = 'Trailing',

SUM({<Date = , DayofDate =, MonthofDate = ,DayofDate = {">=1<=$(=Max(Day((Date))))"} ,MonthofDate = {">=1<=$(=Max(Month(Date)))"}>}Amt)

,if(Only({1}PeriodRange) = 'Selected',

SUM({<Date = {"$(=Max(Date))"}, DayofDate =, MonthofDate = >}Amt)

))

=if(Only({1}PeriodRange) = 'Trailing',

SUM({<Date = , DayofDate =, MonthofDate = ,DayofDate = {">=1<=$(=Max(Day((Date))))"} ,MonthofDate = {">=1<=$(=Max(Month(Date)))"}>}Amt1)

,if(Only({1}PeriodRange) = 'Selected',

SUM({<Date = {"$(=Max(Date))"}, DayofDate =, MonthofDate = >}Amt1)

))

Re: How to display dynamic column as per date criteria ?

vinieme12 — Wed, 28 Dec 2016 15:54:53 GMT

Fab as always stalwar1‌

But can I ask you , why do we still use only() on PeriodRange, which is an dummy dimension when the Actual selection is on the Date field?

Re: How to display dynamic column as per date criteria ?

vinieme12 — Wed, 28 Dec 2016 15:56:21 GMT

sadhan.damal‌, now you have a complete dynamic solution, use the expressions below by Sunny

Re: How to display dynamic column as per date criteria ?

sunny_talwar — Wed, 28 Dec 2016 15:58:01 GMT

Here you will see why

When 4/19/2013 is selected PeriodRange is only available for that day, rest of the days it is null. When I used {1}, regardless if your other selections, it is always available.

HTH

Best,

Sunny