Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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.

    

IDDateField 1If Statement
0011/1/201767%0
0021/10/201775%0
0031/11/2017100%1
0041/12/2017100%1
0051/25/2017100%1
0012/1/2017100%1
0022/3/201785%0
0032/16/2017100%1
0042/21/2017100%1
0052/25/2017100%1

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

JAN = 3

FEB = 4

any ideas ?

5 Replies
ahaahaaha
Honored Contributor

Re: Aggr the if else

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

Not applicable

Re: Aggr the if else

Thank you, Andrey for the quick response.

But I just found out that I have another scenario.

       

       

IDDateNumeratorDenominatorField 1If Statement
11/1/201730045067%0
21/10/201745060075%0
31/11/2017500500100%1
41/12/2017600600100%1
51/12/2017560560100%1
61/25/2017250250100%1
71/25/201731035088%0
12/1/2017380380100%1
22/3/201759570085%0
32/3/2017500500100%1
42/16/2017550550100%1
52/21/2017400400100%1
62/25/2017220220100%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

MVP
MVP

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:

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

Regards,
Prashant Sangle
Not applicable

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

     

ZoneDateNumeratorDenominatorcalculation(not part of the table, this calculation will be applied inside set analysis)
A2/10/2017172085%
A2/11/20172020100%
A2/12/20172020100%
A2/13/20172020100%
A2/14/20172020100%
A

3/1/2017    

152075%
A3/2/2017162080%
A3/3/20172020100%
A3/4/20172020100%
A3/5/20172020100%
B2/10/2017192095%
B2/11/20172020100%
B2/12/20172020100%
B2/13/20172020100%
B2/14/20172020100%
B3/1/2017     2020100%
B3/2/20172020100%
B3/3/20172020100%
B3/4/20172020100%
B3/5/20172020100%

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