Data Containing Anything other than 1 or 0 will not Total

[hammermill21]

Hello!

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

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!

[rittermd]

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

[hammermill21]

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

[zebhashmi]

try

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

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

[hammermill21]

Nope doesn't work

[sunny_talwar]

Are you using CrossTable in the script?

[sunny_talwar]

Can you try this

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

[hammermill21]

Yes, I am using a crosstable in the script.

[hammermill21]

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;

[sunny_talwar]

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