topic Re: Set analysis on days between two dates in QlikView

Set analysis on days between two dates

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

Hello there.

I currently have a situation where I have two dates.

Both are defined by expressions.

First date is made by convertering a string, and adding some conditions to it.

Secound date is defined by today(0)

Third coulum I have is firstdate - secounddate which gives me the difference in days.

Now I only want to show the rows where the difference in days are above 100.

I cannot seem to do a set analysis, because I cannot make a sum, maxstring or whatever out of my dates.

Re: Set analysis on days between two dates

swuehl — Tue, 05 Apr 2016 17:27:33 GMT

You can hide the lines below 100 using another expression like

=If( ConvertMyStringExpression - Today(0) > 100, ConvertMyStringExpression - Today(0),0)

Replace ConvertMyStringExpression with your 'First date is made by convertering a string, and adding some conditions to it' expression (without using a column or expression reference).

I assume this expression is located in column(4).

Then all other expressions need to check the result of column(4):

=If(Column(4), Today(0) )

etc.

And check that 'Suppress zero values' is enabled on presentation tab. Which essentially hides all lines in your straight table that show expressions that are all zero or NULL.

Re: Set analysis on days between two dates

Anonymous — Wed, 06 Apr 2016 06:55:27 GMT

Thank you for the help !

It partially worked. First I changed the expression in column 4 to the "if" expression, instead of using column references (can you tell me, why that only works when i do the simple date - date?, but not in the if statement?)

After I had made this change, I only got values above 100, all other values was set to 0, (in the presentation tap, all expression was set to suppress zeros, so how come they still show up?)

Then I changed the other two expressions, to if statements, =If(Column(4), Today(0) ) etc.

After that all the 0 values went away. So now I have the correct list (presumably)!

However something else weird happened, my dates were all turned to null or missing.

How come?

Re: Set analysis on days between two dates

Kushal_Chawda — Wed, 06 Apr 2016 07:05:48 GMT

Try,

=if( [ExpressionLabelOfSecondDate] - [ExpressionLabelOfFirstDate] >100,[ExpressionLabelOfSecondDate] - [ExpressionLabelOfFirstDate] ,0)

Re: Set analysis on days between two dates

perumal_41 — Wed, 06 Apr 2016 07:15:05 GMT

Hi Dennis,

apply direct expression instead of referring column

like below

1 expression

=If( ConvertMyStringExpression - Today(0) > 100,ConvertMyStringExpression-,0)

2 expression

=If( ConvertMyStringExpression - Today(0) > 100,Today(0),,0)

Re: Set analysis on days between two dates

Anonymous — Wed, 06 Apr 2016 07:15:42 GMT

Strangely this gave me a static value in all rows.

Re: Set analysis on days between two dates

swuehl — Wed, 06 Apr 2016 07:20:15 GMT

Your dates are in dimensions or chart expressions?

If the are located in chart expressions (or calculcated dimensions), how do these look like?

Re: Set analysis on days between two dates

Anonymous — Wed, 06 Apr 2016 07:26:39 GMT

They are in the tab expressions.

Expression 1 (out commented the part you told me to put in)

//=If(Column(4),

date(date#(

MaxString({$<[Seneste aktivitet kalenderId]={">$(=0)"},Forløbsstatus={'I gang'}>}[Seneste aktivitet kalenderId])

,'YYYYMMDD'),'DD-MM-YYYY')

//)

Expression 2 (out commented the part you told me to put in):

//=If(Column(4),

Today(0)

//)

Expression 3:

if(Today(0)

date(date#(

MaxString({$<[Seneste aktivitet kalenderId]={">$(=0)"},Forløbsstatus={'I gang'}>}[Seneste aktivitet kalenderId])

,'YYYYMMDD'),'DD-MM-YYYY') > 100,

Today(0)

date(date#(

MaxString({$<[Seneste aktivitet kalenderId]={">$(=0)"},Forløbsstatus={'I gang'}>}[Seneste aktivitet kalenderId])

,'YYYYMMDD'),'DD-MM-YYYY'),0)

Alternative expression 3:

//if([I dag]-[Dage siden sidste aktivitet] >= 100, [I dag]-[Dage siden sidste aktivitet],0)

Re: Set analysis on days between two dates

perumal_41 — Wed, 06 Apr 2016 08:15:50 GMT

Hi Dennis,

Try like below

1 expression

=If(

Today(0)

date(date#(

MaxString({$<[Seneste aktivitet kalenderId]={">$(=0)"},Forløbsstatus={'I gang'}>}[Seneste aktivitet kalenderId])

,'YYYYMMDD'),'DD-MM-YYYY') > 100,ConvertMyStringExpression,0)

2 expression

=If(

Today(0)

date(date#(

MaxString({$<[Seneste aktivitet kalenderId]={">$(=0)"},Forløbsstatus={'I gang'}>}[Seneste aktivitet kalenderId])

,'YYYYMMDD'),'DD-MM-YYYY') > 100Today(0),0)

Re: Set analysis on days between two dates

Kushal_Chawda — Wed, 06 Apr 2016 08:31:08 GMT

then try

if( ((Expression Of Today ) - (Expression of first day))>100,

(Expression Of Today ) - (Expression of first day),0 )

Note: Expression here is the actual expression

Re: Set analysis on days between two dates

Anonymous — Wed, 06 Apr 2016 10:22:15 GMT

That gave me the same result i had previously where all values become 42460

Re: Set analysis on days between two dates

swuehl — Wed, 06 Apr 2016 11:45:42 GMT

Dennis,

If you only have three expressions, and the third is the one to check, use

If( Column(3),

...

in the other two expressions.

Re: Set analysis on days between two dates

Anonymous — Wed, 06 Apr 2016 12:13:36 GMT

Ahhh!

So I thought that when counting columns the first dimension column counted as well...Why doesn't it?

Well thank you! that solved the problem!

Re: Set analysis on days between two dates

Anonymous — Wed, 06 Apr 2016 13:24:41 GMT

What if I wanted to make the whole:

=Today(0)

date(date#(MaxString({$<[Seneste aktivitet kalenderId]={">$(=0)"},Forløbsstatus={'I gang'}>}[Seneste aktivitet kalenderId]),'YYYYMMDD'),'DD-MM-YYYY')

Into a variabel? I tried to do that, and make an expression saying if(variabel > 100, variabel).

But it didn't work, also changed the checking expression in the other column to put the variabel instead of the long expression.

It might help in other cases just to use this statement as a variabel.

Re: Set analysis on days between two dates

swuehl — Wed, 06 Apr 2016 15:30:07 GMT

It might work with a variable, but it depends on how the variable is defined an called, i.e. what the scope of the variable evaluation is:

The Little Equals Sign

The Magic of Dollar Expansions

The Magic of Variables