topic Re: Select in Field - Calculated Dates in QlikView

Select in Field - Calculated Dates

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have 2 dates (Date A, Date B). I am creating a button wherein once pressed it would do both of the following actions in one button:

Select in Field [Date B] - select all Date B where month of Date B is this month or where month of Date B is next month

Select in Field [Date A] - if Year of Date B is 2050, select all Date A where month of Date A is same as month of Today() (regardless of the year)

I am able to do this as a calculated expression. But when creating a button for it, I am unable to translate it.

Sample data:

Record ID	Date A	Date B
ABC	1/1/2018	4/15/2018
DEF	1/1/2018	5/31/2018
GHI	1/1/2018	3/31/2019
JKL	1/1/2018	9/1/2017
MNO	4/1/2000	12/31/2050

If today is April 11, 2018, then pressing the button should select the following:

ABC because Date B is this month

DEF because Date B is next month

MNO because Year of Date B is 2050 AND month of Date A is same as MonthNum of Today() (i.e. April or 4) regardless of whether the year is the same is this year.

GHI JKL will not be selected as they do not satisfy any of the above parameters.

Thanks!

Re: Select in Field - Calculated Dates

crusader_ — Wed, 11 Apr 2018 07:10:22 GMT

Hi,

Select smth in field action requires concrete value which exists in data model.

I would suggest you to create a flag in script and select 0 or 1 in action under your button.

Of course assuming you reload the app on daily basis.

Hope this helps.

//Andrei

Re: Select in Field - Calculated Dates

Anonymous — Wed, 11 Apr 2018 07:17:56 GMT

Ah yes I was hoping there is a way without having to create a dummy field for it in the load. But I suppose that's the ideal approach.

Re: Select in Field - Calculated Dates

Anonymous — Wed, 11 Apr 2018 07:48:47 GMT

Is modifying the load script an option? If you can then, add an extra column called FlagCheck in the load script. Here you can add all your conditions and populate that flag. In your button level, all you have to do is select flagcheck Y.

See screenshot:

Here's the load script:

------------------------------------------------------------------------------------------------------

Sampledata:

Load * inline [

Record ID, Date A, Date B

ABC, 1/1/2018, 4/15/2018

DEF, 1/1/2018, 5/31/2018

GHI, 1/1/2018, 3/31/2019

JKL, 1/1/2018, 9/1/2017

MNO, 4/1/2000, 12/31/2050

];

SampleDataV2:

Load

if(YearB = 2050, 'Y',

if(YearB = Year(today()) and (MonthB = month(today()) or MonthB = Month(date#(month(today())+1, 'M'))), 'Y')) as FlagCheck,

;

load

month([Date B]) as MonthB,

Year([Date B]) as YearB,

resident Sampledata;

drop table Sampledata;

--------------------------------------------------------------------------------------------------

In the button:

Let me know if this helps. Else we'll work on the button

Rgds,

Benazir

*Edit: I didn't realize the thread had progressed while I was typing my reply*

Re: Select in Field - Calculated Dates

Anonymous — Wed, 11 Apr 2018 08:27:08 GMT

Thanks! I modified

if(YearB = 2050, 'Y',

into

if(YearB = 2050 and MonthA = month(today()), 'Y',

since if Year of Date B is 2015, need to look at the month of Date A.

Re: Select in Field - Calculated Dates

Anonymous — Wed, 11 Apr 2018 08:28:39 GMT

Happy to help

*and oops, sorry about the if condition*

Rgds,

Benazir