Expression with calculated field

[Anonymous]

Hello,

I have the following table :

ID	Old Date	New Date	Difference
2	10/10/2018	10/10/2018	0
5	10/10/2018	15/10/2018	-5

My "Difference field is defined thanks to the following expression :

=If(NetWorkDays([Old Date],[New Date])>0,

NetWorkDays([Old Date],[New Date])-1,

-NetWorkDays([New Date]],[Old Date)+1)

I would like to add a KPI showing the number of ID that have the "Difference" field different to 0.

I tried the following expression :

Count({< If(NetWorkDays([Old Total Date début],[New Total Date début])>0,

NetWorkDays([Old Total Date début],[New Total Date début])-1,

-NetWorkDays([New Total Date début],[Old Total Date début])+1)<>{0}>} MSN)

But it's not working, I'm not very familiar with Set modifiers.

Could I give a name to the expression in blue in order to use it again ? I created a custom measure but doesn't seem to work if I want to use the name of my measure.

Best regards,

Caroline

[sunny_talwar]

Try this

=Count(DISTINCT {<ID = {"=If(NetWorkDays([Old Date],[New Date]) > 0, NetWorkDays([Old Date],[New Date])-1, -NetWorkDays([New Date]],[Old Date)+1) <> 0"}>} ID)

[Anonymous]

Doesn't seem to work.. The number shown is not the good one..

[sunny_talwar]

I modified my expression after my initial response... did you try this and it doesn't give the right number?

=Count(DISTINCT {<ID = {"=If(NetWorkDays([Old Date],[New Date]) > 0, NetWorkDays([Old Date],[New Date])-1, -NetWorkDays([New Date]],[Old Date)+1) <> 0"}>} ID)

[Anonymous]

Thanks but I tried this one and the number shown is always zero...

I would like my KPI to adapt to other filters so should I put "$" before "<ID" ?

[sunny_talwar]

Unless you have alternate states in your dashboard, not having $ is equivalent to $...

[teiswamsler]

Hi Caroline

Is the purpose of the analyse, to only show/count the ID with a changed date?    

/Teis

[Anonymous]

I have a long list of references and I would like to have a KPI to know how much reference have a changed date, yes

[teiswamsler · Accepted solution]

Hi Caroline

In a table chart add above dimension in your description try add expression

Count( If(  NewDate <> OldDate, 1, null() ) ) as CountID // this with count Id with a changed date

in table preferance deselec show z values

In a KPI chart try add expression

Count( aggr( If(  NewDate <> OldDate, 1, null() ), ID) )

/teis

[Anonymous]

I will try that, thanks !

If I add a calculated field in my table, do I have to add it again every time i load data ?