Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

calculated measure help

Hi  all.

I have a source table and in this there are multiple rows per id according to different statuses of another field and also a date field, so data looks like this

ClientID   Date             Result  Status      OrderID

1                 1/1/2020         0                  A                  1

1                 1/1/2020         1                 A                   2

1                1/1/2020         0                  B                   3

1                2/1/2020         1                  A                   4

2                1/1/2020         0                  C                   5

2                  1/1/2020         0                 A                   6

etc

What I was asked to is to make temporary tables of the above data for each Status like this:

Temp_Status_A:


LOAD ClientID,
max(OrderID) ,
max(Date)
resident Source_Table
where Status='A'
group by ClientID;

 

and so on for each status.

Now I am asked to count how many clients had at least a result=0  examining each status, but only taking into account the last Date for each one.

I tried

count(aggr(count({<[Date]={"=max("Date")"},[Result]={'0'}, [Status]={'A'}>+[Date]={"=max("Date")"},[Result]={'0'}, [Status]={'B'}>*[Date]={"=max("Date")"},[Result]={'0'}, [Status]={'C'}>} [ClientID]), [ClientID]))

but doesn't seem right.

I'd rather do it in script but i have no clue how to  with all these new temp tables that don't include result at all (because i need the max date, and grouping by result also may give me two dates with 0 and 1 as results while i just want the max date).

 

Any help is appreciated. 

 

Ioanna

 

 

1 Reply
Highlighted
Creator II
Creator II

Anyone? 🤕