5 Replies Latest reply: Mar 9, 2017 3:40 AM by Michael Angelo Arocena

# Aggr the if else

Hello Qlik Community

I would like to ask if this is possible to qlik sense,

So, I have a IF Statement where if Field1 is equal to 100%, then 1 else 0.

 ID Date Field 1 If Statement 001 1/1/2017 67% 0 002 1/10/2017 75% 0 003 1/11/2017 100% 1 004 1/12/2017 100% 1 005 1/25/2017 100% 1 001 2/1/2017 100% 1 002 2/3/2017 85% 0 003 2/16/2017 100% 1 004 2/21/2017 100% 1 005 2/25/2017 100% 1

Now, how can I sum these all 1s and show it thru KPI chart.

JAN = 3

FEB = 4

any ideas ?

• ###### Re: Aggr the if else

Hi Michael,

Script code

Directory;

Date,

[Field 1],

[If Statement]

FROM

On sheet create new table/

Dimension: Month

Expression: Count ({<[Field 1]={'100%'}>}ID)

Regards,

Andrey

• ###### Re: Aggr the if else

Thank you, Andrey for the quick response.

But I just found out that I have another scenario.

 ID Date Numerator Denominator Field 1 If Statement 1 1/1/2017 300 450 67% 0 2 1/10/2017 450 600 75% 0 3 1/11/2017 500 500 100% 1 4 1/12/2017 600 600 100% 1 5 1/12/2017 560 560 100% 1 6 1/25/2017 250 250 100% 1 7 1/25/2017 310 350 88% 0 1 2/1/2017 380 380 100% 1 2 2/3/2017 595 700 85% 0 3 2/3/2017 500 500 100% 1 4 2/16/2017 550 550 100% 1 5 2/21/2017 400 400 100% 1 6 2/25/2017 220 220 100% 1

Field 1 = Numerator / Denominator

In the table above, we have 2 records for 1/12/2017, since both records reach 100%, we count the date as 1, even with different IDs (we are not counting the ID). For 1/25/2017, since 1 record did not attain 100% (ID #7) , we cannot consider it to add even ID #6 attain 100%.

We are counting days, days that every record reaches 100%

Now, how can I sum these all 1s and show it thru KPI chart.

JAN = 2 (because only 1/11/2017 and 1/12/2017 reaches 100%)

FEB = 4 (because only 2/1/2017, 2/16/2017, 2/21/2017, 2/25/2017)

Regards,

mike

• ###### Re: Aggr the if else

Assuming you have a Month field as well as Date Field, this should work:

Dimension: Month

Expression: =Sum(Aggr(If(Min([Field 1]) = 1, 1), Date, Month))

(Assumes that Field 1 has values 0 - 1 formatted as %)

• ###### Re: Aggr the if else

Hi,

You can try like this in script.

Test:

Date,

Numer,

Denom

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join

Resident Test

group by Date;

then in chart take expression like

count(DISTINCT if(%age=1,Date))

Regards,

Prashant Sangle

• ###### Re: Aggr the if else

Thank you guys , really appreciate your help.

One last thing and I think who can answer this will be the correct answer.

so I have a table

 Zone Date Numerator Denominator calculation(not part of the table, this calculation will be applied inside set analysis) A 2/10/2017 17 20 85% A 2/11/2017 20 20 100% A 2/12/2017 20 20 100% A 2/13/2017 20 20 100% A 2/14/2017 20 20 100% A 3/1/2017 15 20 75% A 3/2/2017 16 20 80% A 3/3/2017 20 20 100% A 3/4/2017 20 20 100% A 3/5/2017 20 20 100% B 2/10/2017 19 20 95% B 2/11/2017 20 20 100% B 2/12/2017 20 20 100% B 2/13/2017 20 20 100% B 2/14/2017 20 20 100% B 3/1/2017 20 20 100% B 3/2/2017 20 20 100% B 3/3/2017 20 20 100% B 3/4/2017 20 20 100% B 3/5/2017 20 20 100%

I want to count the dates which it hit 100% based on the calculation (Numerator/Denominator). Here comes the challenge, I will have a "Month" filter. For this scenario, the user click "March 2017", that means it previous month is "February 2017". So everytime the user filter the months, the "Month -1" always previous of "Month". Expected Output is.

Month - 1         Month

Zone A =         4                   3

Zone B =         4                   5

Sorry, I'm still learning Qlik. But thank you guys!

Regards,

Mike