topic Re: Re: Re: Set Analysis in QlikView 10, variable problem in QlikView

Set Analysis in QlikView 10, variable problem

ob — Thu, 24 Jul 2014 13:27:58 GMT

Hi,

I have this expression:

=TextCount({$<DUEDATE={"<$(=Date(Today()+0,'DD.MM.YYYY hh:mm:ss'))"},COMPLETEDVOIDED={0}>} DISTINCT WORKORDER)

If I try to use a variable (with the value 0) instead of the 0, I get wrong result:

=TextCount({$<DUEDATE={"<$(=Date(Today()+$(NumberOfDays),'DD.MM.YYYY hh:mm:ss'))"},COMPLETEDVOIDED={0}>} DISTINCT WORKORDER)

Can anyone see what is wrong?

Thanks!

Øystein Birkeland

Re: Set Analysis in QlikView 10, variable problem

Thu, 24 Jul 2014 15:22:47 GMT

Why dont you store both Today()+ variable into a variable say vValue and then use that variable

TextCount({$<DUEDATE={"<$(vValue)"},COMPLETEDVOIDED={0}>} DISTINCT WORKORDER)

Attached is a simple example. Please take a look at the variable created in Settings called vDate and the expression.

Re: Set Analysis in QlikView 10, variable problem

ob — Fri, 25 Jul 2014 13:39:51 GMT

Thanks!

Seems to work in your enclosed example, but not in mine, I'm afraid! I guess it then have to do with the date format of DUEDATE and the variable vValue.

DUEDATE comes from a Oracle Select like this "TRUNC( table.workorder_duedate AS DUEDATE".

In my loading script I have defined vValue just to let it have a default value: LET vValue = Today();

My expression looks like this now:

=TextCount({$<DUEDATE={"<$(vValue)"},COMPLETEDVOIDED={0}>} DISTINCT WORKORDER )

Any thoughts?

Re: Set Analysis in QlikView 10, variable problem

Fri, 25 Jul 2014 13:45:20 GMT

Possible to attach a sample file.

Re: Re: Set Analysis in QlikView 10, variable problem

ob — Fri, 25 Jul 2014 14:00:38 GMT

Thanks!

I've attached the app. You will find the expression on the Gauge, Properties, Presentation, Text in Chart. The correct number should be 1579 since all records have a duedate that is earlier than today!

With the slider I set the variable numberOfDays that should be added to Today() !

Re: Re: Re: Set Analysis in QlikView 10, variable problem

Fri, 25 Jul 2014 14:23:37 GMT

Attached with answer.

Your variable needs to be

vValue

=Timestamp(Today()+numberOfDays)

Re: Re: Re: Set Analysis in QlikView 10, variable problem

ob — Mon, 28 Jul 2014 07:53:19 GMT

Thank you very much! This works fine!