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!

[hammermill21]

I attached the excel to the original message.

Thank you for helping, I'm not sure what I did wrong.

[sunny_talwar · Accepted solution]

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;

[hammermill21]

Yes I was going to answer you once I finished the other one also!

I see how it works for you, but when I do it with my connection to SmartSheet it doesn't work

[sunny_talwar]

That I can't really help you with. I can neither do it at my end and never had to do it... so, no for me to suggest anything. Only thing I can tell you is that you need to somehow bring those EScore's in your dashboard... because your expression is not wrong

[hammermill21]

I figured, but it's ok what you send me is really helpful! Thank you so much!!

It gives me a basic understanding of what I need to do.