topic Re: Date Variable Set Analysis in QlikView

Date Variable Set Analysis

wonkymeister — Fri, 03 Aug 2018 12:01:34 GMT

Help please,

I need to subtract one date from another (which is fine)

But the date that i am subtracting from is in a variable (vPeriodEnd), and my expression isn't working (it works fine with a standard field, its the variable that is causing me the headache). The date held within the variable is ok, as is the format.

any ideas?

if(

only({<

month=

,year=

,absence = {'Sickness'}

,census_date={'>=$(=$(vPeriodStart)) <=$(=$(vPeriodEnd))'}

>}$(vPeriodEnd))

only({<

month=

,year=

,absence = {'Sickness'}

,census_date={'>=$(=$(vPeriodStart)) <=$(=$(vPeriodEnd))'}

>}start_date)

> 28, 'Yes', 'No')

Re: Date Variable Set Analysis

sunny_talwar — Fri, 03 Aug 2018 12:15:23 GMT

What exactly is behind vPeriodEnd? Is it an expression or a hard coded value?

Re: Date Variable Set Analysis

sibin_jacob — Fri, 03 Aug 2018 12:18:22 GMT

Please try the below expression.

if(

only({<

month=

,year=

,absence = {'Sickness'}

,census_date={">=$(=vPeriodStart) <=$(=vPeriodEnd)"}

>}$(vPeriodEnd))

only({<

month=

,year=

,absence = {'Sickness'}

,census_date={">=$(=vPeriodStart) <=$(=vPeriodEnd)"}

>}start_date)

> 28, 'Yes', 'No')

Re: Date Variable Set Analysis

wonkymeister — Fri, 03 Aug 2018 12:19:31 GMT

a date (dd/mm/yyyy)

the user gets to select an end date using a slider on the dashboard to choose the period that they wish to view.

so essentially im subtracting vPeriodEnd - the database field start_date

Re: Date Variable Set Analysis

wonkymeister — Fri, 03 Aug 2018 12:21:33 GMT

Thanks Sibin,

tried that already, doesnt work.

Re: Date Variable Set Analysis

sunny_talwar — Fri, 03 Aug 2018 12:22:20 GMT

So, via a slider a value is set for the date? May be see if this works

if(

only({<

month=

,year=

,absence = {'Sickness'}

,census_date={'>=$(=$(vPeriodStart)) <=$(=$(vPeriodEnd))'}

>} Date#($(vPeriodEnd), 'DD/MM/YYYY'))

only({<

month=

,year=

,absence = {'Sickness'}

,census_date={'>=$(=$(vPeriodStart)) <=$(=$(vPeriodEnd))'}

>}start_date)

> 28, 'Yes', 'No')

Re: Date Variable Set Analysis

vishsaggi — Fri, 03 Aug 2018 12:59:52 GMT

May be give it a try: using sunny's formatting around your variable in single quotes?

if(

only({<

month=

,year=

,absence = {'Sickness'}

,census_date={'>=$(=$(vPeriodStart)) <=$(=$(vPeriodEnd))'}

>} Date#('$(vPeriodEnd)', 'DD/MM/YYYY'))

Re: Date Variable Set Analysis

wonkymeister — Fri, 03 Aug 2018 13:52:21 GMT

No, still not working. Proper head scratcher this one.

Re: Date Variable Set Analysis

sunny_talwar — Fri, 03 Aug 2018 13:58:27 GMT

Would you be able to share a sample to show what exactly you are doing?

Re: Date Variable Set Analysis

BalaBhaskar_Qlik — Fri, 03 Aug 2018 14:40:59 GMT

Is Variable declarations of: vPeriodStart, vPeriodEnd matched with Census_date?

Re: Date Variable Set Analysis

wonkymeister — Fri, 03 Aug 2018 14:46:56 GMT

yes - all matched,

Re: Date Variable Set Analysis

wonkymeister — Thu, 09 Aug 2018 14:22:54 GMT

sorry guys - this was a total mistake on my part - the set was fine, the issue was with logic outside of the expression.

sorry guys and thanks to every one who contributed!