topic Re: count items for max date and specific values in QlikView

count items for max date and specific values

Mon, 30 Nov 2015 18:27:04 GMT

Hello

I've the following data set. Cant quite figure out how to only count ID's with Id status values:3, 24, 12, 25, 31, 43, 56, 57 and max date.

An ID can fall under >1 group during its lifecycle so in the case of ID#7532767 it was a member of group C and D but because D has a greater timestamp I'd like to count only for the highlighted record.

Id like to place this in a straight table with only columns: Group and Count.

I tried using: count( distinct if(
Match( Id Status,3, 24, 12, 25, 31, 43, 56, 57),
aggr(max(Date),Id Status))
)

however I still get group C and D, so im double counting.

How can I return only the highlighted records below from the dataset?

Thank you.

Re: count items for max date and specific values

JonnyPoole — Mon, 30 Nov 2015 18:51:38 GMT

An expression like the following worked for me. Assume Group is productID. You would need to pass all dimensions to each AGGR() in the below expression. I just had 2 dimensions (Date and Product ID) so i passed just 2

sum(

if( aggr(only(Date),Date,[Product ID]) = aggr(max( total <[Product ID]> Date),Date,[Product ID]) ,1))

Re: count items for max date and specific values

MK_QSL — Mon, 30 Nov 2015 19:08:56 GMT

You can create a MaxDateFlag as below

SET TimeStampFormat = 'DD/MM/YYYY h:mm';

Data:

Load Group, ID, [ID Status], Date, Sales From YourTable;

Left Join (Data)

Load ID, TimeStamp(Max(Date)) as MaxDate Resident Data

Where Match([ID Status],3,12,24,25,31,43,56,46)

Group By ID;

Left Join (Data)

Load ID, IF(Date = MaxDate, 1,0) as MaxDateFlag Resident Data

Where Match([ID Status],3,12,24,25,31,43,56,46)

Group By ID;

Drop Field MaxDate;

Now you can use as below

COUNT({<MaxDateFlag = {1}>}Date)

Re: count items for max date and specific values

Mon, 30 Nov 2015 19:18:13 GMT

Hi Jonathan

Thanks for responding.

Im still getting 2 records, here is my expression:

sum(
if(
Match( Id Status,3, 24, 12, 25, 31, 43, 56, 57) AND
aggr(only(Date),Date,Id Status,Group)=
aggr(max(TOTAL<Group> Date), Date,Id Status,Group)
,1
)
)

Re: count items for max date and specific values

Mon, 30 Nov 2015 20:57:15 GMT

Thanks Manish

Im getting an error on the second left join(data).

shouldn't this statement be included in the group by: IF(Date = MaxDate, 1,0)

Re: count items for max date and specific values

MK_QSL — Mon, 30 Nov 2015 21:07:55 GMT

Provide your script here.. I don't think so 2nd Left Join need Group By..

Re: count items for max date and specific values

MK_QSL — Mon, 30 Nov 2015 21:10:52 GMT

Or use something like below

Data:

Load ID, [ID Status], Date From YourtableName;

Left Join

Load ID, TimeStamp(Max(Date)) as MaxDate Resident Data

Where Match([ID Status],3,24,12,25,31,43,56,57)

Group By ID;

Final:

Load *, If(Date = MaxDate,1,0) as MaxDateFlag Resident Data

Where Match([ID Status],3,24,12,25,31,43,56,57);

Drop Table Data;

Re: count items for max date and specific values

JonnyPoole — Mon, 30 Nov 2015 21:45:50 GMT

Move the ID status filters into set analysis as follows

sum( {<[Id Status]={3, 24, 12, 25, 31, 43, 56, 57}>}
if(

aggr(only( {<[Id Status]={3, 24, 12, 25, 31, 43, 56, 57}>} Date),Date,[Id Status],Group)=
aggr(max(TOTAL<Group> {<[Id Status]={3, 24, 12, 25, 31, 43, 56, 57}>} Date), Date,[Id Status],Group)
,1
)
)

Re: count items for max date and specific values

Tue, 01 Dec 2015 17:56:40 GMT

thank you for your help