14 Replies Latest reply: Dec 8, 2017 6:50 AM by Neha Deshmukh

Logic with set of rules

Hi,

We have a logic in qlikiew

Logic:

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

Formula 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.

What would the qlikview logic followed with the above rules

• Re: Logic with set of rules

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

• Re: Logic with set of rules

Hi,

This is sample logic.

Lets say

Logic:

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

Thanks..

• Re: Logic with set of rules

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

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

• Re: Logic with set of rules

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

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

Hi,

Please find the attached sample data set.

Thanks..

• Re: Logic with set of rules

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

Thanks..

• Re: Logic with set of rules

• Re: Logic with set of rules

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

Hi All,

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

Hi,

Any other approach.

Thanks..

• Re: Logic with set of rules

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