Set analysis on days between two dates

[Anonymous]

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.

[swuehl · Accepted solution]

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.

[Anonymous]

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?

[Kushal_Chawda]

Try,

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

[perumal_41]

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)

[Anonymous]

Strangely this gave me a static value in all rows.

[swuehl]

Your dates are in dimensions or chart expressions?

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

[Anonymous]

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)

[perumal_41]

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)

[Kushal_Chawda]

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