Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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
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
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 %)
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
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