Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)) |
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)
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
)
)
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)
Provide your script here.. I don't think so 2nd Left Join need Group By..
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;
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
)
)
thank you for your help