topic Re: Using =if(isnum( with Set Analysis in QlikView

Using =if(isnum( with Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Using =if(isnum( with Set Analysis

sunny_talwar — Thu, 16 Jun 2016 09:47:27 GMT

May be this:

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

Re: Using =if(isnum( with Set Analysis

Thu, 16 Jun 2016 09:57:43 GMT

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.

Re: Using =if(isnum( with Set Analysis

jozisvk11 — Thu, 16 Jun 2016 09:58:41 GMT

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

Re: Using =if(isnum( with Set Analysis

sunny_talwar — Thu, 16 Jun 2016 10:03:04 GMT

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))

Re: Using =if(isnum( with Set Analysis

er_mohit — Thu, 16 Jun 2016 10:09:03 GMT

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))

Re: Using =if(isnum( with Set Analysis

Thu, 16 Jun 2016 10:11:42 GMT

That's worked.

Thanks so much Sunny

Re: Using =if(isnum( with Set Analysis

sunny_talwar — Thu, 16 Jun 2016 10:18:37 GMT

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.