Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using =if(isnum( with Set Analysis

I'm using the following expression within a chart...

=if(isnum([WO Completed Date]),sum({<[WO KPI Priority] = {'Emergency Repairs'}>}WOOntimeIncDelayCounter))/if(isnum([WO Completed Date]),sum({<[WO KPI Priority] = {'Emergency Repairs'}>}WOCounter))

...and the graph is returning 'No data to display'.  The original expression was...

=sum({<[WO KPI Priority] = {'Emergency Repairs'}>}WOOntimeIncDelayCounter)/sum({<[WO KPI Priority] = {'Emergency Repairs'}>}WOCounter)

...and this works okay, but I want it to sum only if the WO Completed Date contains a number. When it doesn't contain a number it contains NULL.

Advice anyone?

1 Solution

Accepted Solutions
sunny_talwar

IsNum() will return -1 or 0 when evaluated. Try this:

=Sum({<[WO KPI Priority] = {'Emergency Repairs'}>} If(IsNum([WO Completed Date]), WOOntimeIncDelayCounter))/Sum({<[WO KPI Priority] = {'Emergency Repairs'}>} If(IsNum([WO Completed Date]), WOCounter))

View solution in original post

7 Replies
sunny_talwar

May be this:

=Sum({<[WO KPI Priority] = {'Emergency Repairs'}>}WOOntimeIncDelayCounter)/Sum({<[WO KPI Priority] = {'Emergency Repairs'}>}WOCounter) * -1 * IsNum([WO Completed Date])

Not applicable
Author

Thanks for responding so promptly Sunny.

I've tried that and I get my graph visual back but it's returning 0.00% when it should be 97.65%.

Can you tell me what the -1 is actually doing?

Mel.

jozisvk11
Creator
Creator

Hello, I have problem with set analyse. I want to filter only values, where is the rows max. I have this, but it is not good.

Sum ({<[Rank of line] = {"$(=max([Rank of line])"} >} [Sum of income]). Thanks for helping me.

Here is my table.

ID Purchase          Row          Income  

N01                      1                  200

N02                      1                  50
N02                      2                  70

N02                      3                  60

N03                      1                  10

N03                      2                  35

N04                      1                  10

N04                      2                  40

N04                      3                  60

N04                      4                  90

N05                     1                  20

Set Analayse should choose me only this table.

ID Purchase          Row          Income  

N01                      1                  200

N02                      3                  60

N03                      2                  35

N04                      4                  90

N05                      1                  20

sunny_talwar

IsNum() will return -1 or 0 when evaluated. Try this:

=Sum({<[WO KPI Priority] = {'Emergency Repairs'}>} If(IsNum([WO Completed Date]), WOOntimeIncDelayCounter))/Sum({<[WO KPI Priority] = {'Emergency Repairs'}>} If(IsNum([WO Completed Date]), WOCounter))

er_mohit
Master II
Master II

Hi

Have you tried this

=if(not isnull([WO Completed Date]),sum({<[WO KPI Priority] = {'Emergency Repairs'}>}WOOntimeIncDelayCounter))/if(isnum([WO Completed Date]),sum({<[WO KPI Priority] = {'Emergency Repairs'}>}WOCounter))

Not applicable
Author

That's worked.

Thanks so much Sunny

sunny_talwar

Please start a new thread for your issue rather than using pre-existing threads for your question. This way you will get greater visibility to your question.