Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
teiswamsler
Partner - Creator III
Partner - Creator III

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

View solution in original post

11 Replies
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
Not applicable
Author

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
Not applicable
Author

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
Partner - Creator III
Partner - Creator III

Hi Caroline

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

/Teis

Anonymous
Not applicable
Author

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
Partner - Creator III
Partner - Creator III

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
Not applicable
Author

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 ?