Logic with set of rules

[nareshthavidishetty]

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

[neha_shirsath]

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

[Anil_Babu_Samineni]

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

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

[nareshthavidishetty]

Hi,

This is sample logic.

Lets say

Logic:

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

Thanks..

[neha_shirsath]

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.

[Anil_Babu_Samineni]

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)

[jonathandienst]

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.

[nareshthavidishetty]

Hi,

Please find the attached sample data set.

Thanks..

[neha_shirsath]

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.

[nareshthavidishetty]

Please provide logic.

I don't have access to open external Apps.

Thanks..