Qlikview aggregation/grouping query

[#1]

Hi all,

I'm fairly new to Qlikview and I have the following data that I am trying to aggregate and count and I'm struggling at the moment!

Each item number has a state that changes over time so for example DC001 has moved from Raised to Submitted to Passed, so it's current state is Passed. I would like to count the current state of the items for the selected project so for this example, 2 are Submitted, 2 are Repaired, 2 are Passed,  and 1 is No Description. I've tried using FirstSortedValue and Aggr on date, but it never seems to do exactly what I want it to do, and when I then add Event Description as a selection and pick a value, then it doesn't show the counts correctly. In addition, I need a separate table that shows any items that have Failed at any point, plus their current state, so for example DC003 has failed, but it is now Repaired. I've managed to get the data to display correctly if I update the load script to select on max(Date), but this then messes up another sheet, so I'd rather do it on the sheet.

Using FirstSortedValue here (FirstSortedValue([Event Description], -num([Date Request Modified])), but not sure how to then group by Event Description to count items

What I'd like to show is this (this was done by changing the load script):

This shows the number of items that have failed using Count({$<[Event Description]={'Failed'}>}[Item No]), but I'd like to add a column showing current state as well

[robert99 · Accepted solution]

I think you woudl need ot do something like this

Load (Data) including

Project & '/' & ItemNo as ID,

and then do a

JOIN (Data)

Load

ID,

MAX (Date Request Modification) as maxDate

group by ID;

THEN DO A TABLE

DIMENSIONS

ITEM / ITEM NO  / Desription

Expression

count (if (Date = maxDate ,Date))

or

Dimension ===  dESCRIPTION

Expression  ====  count (if (Date = maxDate ,Item No))  Item No could be replaced with any field

This will just count the number of lines where  Date= MaxDate (I think)

[#3]

Hi RJ,

That did the trick, thanks for your help! I ended up doing three joins, due to the data model, but all working correctly now

Cheers

Damian