Set Analysis in QlikView 10, variable problem

[ob]

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

[#2]

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

like

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.

[ob]

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?

[#4]

Possible to attach a sample file.

[ob]

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

[#6 · Accepted solution]

Attached with answer.

Your variable needs to be

vValue

=Timestamp(Today()+numberOfDays)

[ob]

Thank you very much! This works fine!