New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor III

Expression with calculated field

Hello,

I have the following table :

IDOld DateNew DateDifference
210/10/201810/10/20180
510/10/201815/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

Tags (2)
1 Solution

Accepted Solutions Partner

Re: Expression with calculated field

Hi Caroline

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

11 Replies MVP

Re: Expression with calculated field

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)

New Contributor III

Re: Expression with calculated field

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

Re: Expression with calculated field

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)

New Contributor III

Re: Expression with calculated field

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" ? MVP

Re: Expression with calculated field

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

Re: Expression with calculated field

Hi Caroline

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

/Teis

New Contributor III

Re: Expression with calculated field

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  Partner

Re: Expression with calculated field

Hi Caroline

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

New Contributor III

Re: Expression with calculated field

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 ?