Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hammermill21
Contributor II

Data Containing Anything other than 1 or 0 will not Total

Hello!

I have this data on an excel type sheet that I am pulling into Qlik Sense:

Capture5.16.18.PNG

When I total the amount of "1" it works fine, but when I try and count the "R13" I always get 0 when I should get a total of 31. The expression I am using is the following:

=Count({<EScore= {'R1'}>} EScore)

I'm not really sure what it's not working.

Thank you in advance!

1 Solution

Accepted Solutions
MVP
MVP

Re: Data Containing Anything other than 1 or 0 will not Total

For the attached... this should work

Table:

CrossTable(DayDate, EScore, 26)

LOAD

    Facility,

    "Mod Year",

    "Elev Id",

    "Elevator Num",

    "Type",

    "Class",

    "Num Landings",

    "Year",

    Quarter,

    "Month",

    "# Days Operational",

    "% Days Operational (Up Time)",

    "Main Fee",

    "R1 > 24 Hrs",

    "R2 Response > 60 min",

    "R3 Response Entrapment > 15 min",

    "R4 Response after hour > 2 hrs",

    "R5 Response after hour with entrapment > 60 min",

    "R6 Fail 3 Times within 30 days",

    "R7 Annual Inspections POC > 30",

    "R8 5 Yr Inspection POC > 30 days",

    "R9 Deficiencies Not Corrected > 90 days",

    "R10 Audit POC > 24 hrs",

    "R11 Audit POC > 48 Hrs",

    "R12 If R6 then Reocurrance 50% M fee",

    "R13 Lack Parts > 24 Hrs",

    "1",

    "2",

    "3",

    "4",

    "5",

    "6",

    "7",

    "8",

    "9",

    "10",

    "11",

    "12",

    "13",

    "14",

    "15",

    "16",

    "17",

    "18",

    "19",

    "20",

    "21",

    "22",

    "23",

    "24",

    "25",

    "26",

    "27",

    "28",

    "29",

    "30",

    "31"

FROM [lib://Lib/Elevator Performance April.xlsx]

(ooxml, embedded labels, table is [Elevator Performance April]);


Final:

LOAD *,

MakeDate(2018, 4, Trim(DayDate)) as ActualDate,

    If(IsNull(EScore), 0, EScore) as New_Escore

Resident Table;


Drop Table Table;

DROP Field EScore;

Rename Field New_Escore TO EScore;


Capture.PNG

24 Replies
rittermd
Honored Contributor

Re: Data Containing Anything other than 1 or 0 will not Total

Shouldn't it say = "R13" instead of "R1"?

hammermill21
Contributor II

Re: Data Containing Anything other than 1 or 0 will not Total

Within my data I have R13 and R1, regardless it is not working.

zebhashmi
Valued Contributor

Re: Data Containing Anything other than 1 or 0 will not Total

try

=Count({1<EScore= {'R1'}>} EScore)

How is table looking in Qlik it is a cross table Yes?

hammermill21
Contributor II

Re: Data Containing Anything other than 1 or 0 will not Total

Nope doesn't work

MVP
MVP

Re: Data Containing Anything other than 1 or 0 will not Total

Are you using CrossTable in the script?

MVP
MVP

Re: Data Containing Anything other than 1 or 0 will not Total

Can you try this

=Count({<EScore= {"*R13*"}>} EScore)

hammermill21
Contributor II

Re: Data Containing Anything other than 1 or 0 will not Total

Yes, I am using a crosstable in the script.

hammermill21
Contributor II

Re: Data Containing Anything other than 1 or 0 will not Total

Nope that doesn't work either, could something be wrong within my crosstable?

Input:

CrossTable(DayDate,EScore,27)

LOAD

[2018 May Record ID],

    Facility,

    "Mod Year",

    "Elev Id",

    "Elevator Num",

    "Type",

    "Class",

    "Num Landings",

    "Year",

    Quarter,

    "Month",

    "# Days Operational",

    "% Days Operational (Up Time)",

    "Main Fee",

    "R1 > 24 Hrs",

    "R2 Response > 60 min",

    "R3 Response Entrapment > 15 min",

    "R4 Response after hour > 2 hrs",

    "R5 Response after hour with entrapment > 60 min",

    "R6 Fail 3 Times within 30 days",

    "R7 Annual Inspections POC > 30",

    "R8 5 Yr Inspection POC > 30 days",

    "R9 Deficiencies Not Corrected > 90 days",

    "R10 Audit POC > 24 hrs",

    "R11 Audit POC > 48 Hrs",

    "R12 If R6 then Reocurrance 50% M fee",

    "R13 Lack Parts > 24 Hrs",

    "1",

    "2",

    "3",

    "4",

    "5",

    "6",

    "7",

    "8",

    "9",

    "10",

    "11",

    "12",

    "13",

    "14",

    "15",

    "16",

    "17",

    "18",

    "19",

    "20",

    "21",

    "22",

    "23",

    "24",

    "25",

    "26",

    "27",

    "28",

    "29",

    "30",

    "31"

  

RESIDENT [2018 May];

 

Drop Table [2018 May]; 

Final:

Load RecNo() as ID,

[2018 May Record ID],

    Facility,

    "Mod Year",

    "Elev Id",

    "Elevator Num",

    "Type",

    "Class",

    "Num Landings",

    "Year",

    Quarter,

    "Month",

    "# Days Operational",

    "% Days Operational (Up Time)",

    "Main Fee",

    "R1 > 24 Hrs",

    "R2 Response > 60 min",

    "R3 Response Entrapment > 15 min",

    "R4 Response after hour > 2 hrs",

    "R5 Response after hour with entrapment > 60 min",

    "R6 Fail 3 Times within 30 days",

    "R7 Annual Inspections POC > 30",

    "R8 5 Yr Inspection POC > 30 days",

    "R9 Deficiencies Not Corrected > 90 days",

    "R10 Audit POC > 24 hrs",

    "R11 Audit POC > 48 Hrs",

    "R12 If R6 then Reocurrance 50% M fee",

    "R13 Lack Parts > 24 Hrs",

        MakeDate(2018,04,trim(DayDate)) as ActualDate,

        If(isnull(EScore),0,EScore) as EScore

Resident Input;

Drop table Input;

MVP
MVP

Re: Data Containing Anything other than 1 or 0 will not Total

Everything looks good to me... not sure... are you able to share a sample?

Community Browser