Aggr the if else

[#1]

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 ?

[ahaahaaha]

Hi Michael,

Script code

Directory;

LOAD ID,

    Date,

    [Field 1],

    Month(Date) as Month, //add field!

    [If Statement]

FROM

[Path your table];

On sheet create new table/

Dimension: Month

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

Regards,

Andrey

[#3]

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

[jonathandienst]

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

[PrashantSangle]

Hi,

You can try like this in script.

Test:

LOAD Id,

     Date,

     Numer,

     Denom

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join

Load Date,Sum(Numer)/Sum(Denom) as %age

Resident Test

group by Date;

then in chart take expression like

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

Regards,

Prashant Sangle

[#6]

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