topic Re: Logic with set of rules in QlikView

Logic with set of rules

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We have a logic in qlikiew

Logic:

Sales rate: (Sum(Sales)/Count(TransID))*100000

Formula Rules:

If the numerator is greater than zero, but the denominator is zero, then the Sales rate is set to 1,000,000.
If the numerator and denominator are both zero, then the Sales rate is zero.
If the denominator is negative and the numerator is 0, then the Sales rate is set to 0.
If the denominator is negative and the numerator is greater than zero, then the Sales rate is set to 1,000,000.

What would the qlikview logic followed with the above rules

Re: Logic with set of rules

neha_shirsath — Thu, 07 Dec 2017 09:26:00 GMT

How is it possible that denominator i.e count(TransID) has negative values?

Re: Logic with set of rules

Anil_Babu_Samineni — Thu, 07 Dec 2017 09:27:01 GMT

Try to close Qlik Community Tip: Marking Replies as Correct or Helpful‌.

Not sure i followed in you. Will you mock something..

Re: Logic with set of rules

nareshthavidishetty — Thu, 07 Dec 2017 09:28:20 GMT

Hi,

This is sample logic.

Lets say

Logic:

(Sum(sales)/count(parts))*1000000

Thanks..

Re: Logic with set of rules

neha_shirsath — Thu, 07 Dec 2017 09:32:30 GMT

Can you post some sample data?

Because might be i'm not getting your point. What i'm thinking is if you are counting Parts it will be 1,2,3... etc not in negative.

Re: Logic with set of rules

Anil_Babu_Samineni — Thu, 07 Dec 2017 09:32:57 GMT

May be these?

1) If((Sum({<Sales = {"=Sum(Sales)>0"}>} Sales)/Count({<TransID = {"=Count(TransID) = 0"}>}TransID)),(Sum(Sales)/Count(TransID))*100000)

2) If((Sum({<Sales = {"=Sum(Sales)=0"}>} Sales)/Count({<TransID = {"=Count(TransID)=0"}>} TransID)), 0)

3) If((Sum({<Sales = {"=Sum(Sales)=0"}>} Sales)/Count({<TransID = {"=Count(TransID)<=0"}>} TransID)), 0)

4) If((Sum({<Sales = {"=Sum(Sales)>0"}>} Sales)/Count({<TransID = {"=Count(TransID)<=0"}>} TransID)), (Sum(Sales)/Count(TransID))*1000000)

Re: Logic with set of rules

jonathandienst — Thu, 07 Dec 2017 09:37:17 GMT

To handle your first two rules:

=Alt(Sum(Sales) / Count(TransID) * 1E5, If(Sum(Sales) = 0, 0, 1E6))

Dividing by zero would trigger the second part of the Alt().

The last two rules could never happen as Count() cannot produce a negative value.

Re: Logic with set of rules

nareshthavidishetty — Thu, 07 Dec 2017 09:49:06 GMT

Hi,

Please find the attached sample data set.

Thanks..

Re: Logic with set of rules

neha_shirsath — Thu, 07 Dec 2017 10:20:55 GMT

Please find the attached file as per data you have provided.

But here as per logic provided in sample data, Condition 1,2,and 3 will be always 0 because for condition 1 denominator is 0 and condition 2 & 3 rate is 0.

Re: Logic with set of rules

nareshthavidishetty — Thu, 07 Dec 2017 11:18:02 GMT

Please provide logic.

I don't have access to open external Apps.

Thanks..

Re: Logic with set of rules

neha_shirsath — Thu, 07 Dec 2017 11:21:49 GMT

1. If(Sum(Notifications)>0 and sum([Item Movement])=0, (Sum(Notifications)/sum([Item Movement]))*100000)

2.If(Sum( Notifications)=0 and sum([Item Movement])=0, (Sum(Notifications)/sum([Item Movement]))*0)

3.If(Sum( Notifications)=0 and sum([Item Movement])<0, (Sum(Notifications)/sum([Item Movement]))*0)

4.If(Sum( Notifications)>0 and sum([Item Movement])<0, (Sum(Notifications)/sum([Item Movement]))*1000000)

Re: Logic with set of rules

nareshthavidishetty — Thu, 07 Dec 2017 11:33:41 GMT

Hi,

Thanks for the try.

I belive its not what i required.

Kindly find the below set of rules.

1. If the numerator is greater than zero, but the denominator is zero, then the Sales rate is set to 1,000,000.

2. If the numerator and denominator are both zero, then the Sales rate is zero.

3. If the denominator is negative and the numerator is 0, then the Sales rate is set to 0.

4. If the denominator is negative and the numerator is greater than zero, then the Sales rate is set to 1,000,000.

Thanks..

Re: Logic with set of rules

nareshthavidishetty — Thu, 07 Dec 2017 12:20:33 GMT

Hi All,

I belive got the answer.

if(sum(Notifications)>0 and sum([Item Movement])=0,1000000,

if(sum(Notifications)=0 and sum([Item Movement])=0,0,

if(sum(Notifications)=0 and sum([Item Movement])<0,0,

if(sum(Notifications)>0 and sum([Item Movement])<0,1000000,

(sum(Notifications)/sum([Item Movement])*1000000)))))

Is there any way to get same results with out using 'IF' condition.

I would be really great..

Thanks..

Re: Logic with set of rules

nareshthavidishetty — Fri, 08 Dec 2017 09:21:49 GMT

Hi,

Any other approach.

Thanks..

Re: Logic with set of rules

neha_shirsath — Fri, 08 Dec 2017 11:50:52 GMT

You can do this by calculating this logic to script level.