Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Running into a bit of a challenge with something that, logically, should be fairly simple.
Date | JobID | Status |
07/31/2013 | 12 | In Progress |
07/24/2013 | 12 | Closed |
07/20/2013 | 12 | Cancelled |
08/12/2014 | 97 | In Progress |
07/31/2014 | 97 | In Progress |
From this example table, I only want to report on the last entry by JobID, where Status = 'In Progress' - 07/31/2013 for JobID 12 and 08/12/2014 for JobID 97.
I don't want to include any previous JobIDs, regardless of their status.
I would appreciate your assistance with this issue.
I have tried using the Max function in both Dimension and Expression formulas, but don't get the desired result.
My desired result would be:
Year | Month | Status | # of Job IDs |
2013 | July | In Progress | 1 |
2014 | August | In Progress | 1 |
Try using calculated dimension as Date:
aggr(max(if(Status='In Progress', Date)),JobID)
You maybe need JobID as another dimension. I didn't test.
Try using calculated dimension as Date:
aggr(max(if(Status='In Progress', Date)),JobID)
You maybe need JobID as another dimension. I didn't test.
Status. Date and JobId as dimension and try this in your expression:
count(aggr(if(max(TOTAL <JobID> Date)=Date, count({<Status = {'In Progress'}>} JobID)), JobID, Date))
The use of calculated dimensions can work if the amount of data is very small.
I would rather do this in the script.
Do something like this:
Table:
Load Date, JobID, Status from Source.qvd ....;
left join(Table)
load Date,JobID,
if(peek(JobID)=JobID,null(),1) as %ValidJobIDFlag
resident Table
where Status = 'In Progress'
order by Date desc;
Then in your front end you use Year, Month and Status as dimensions and the expression:
Count({<%ValidJobIDFlag={1}>}distinct JobID)
Hope this helps
SKG
Thank you Padmaja Gade, Simen Kind Gulbrandsen and Michael Solomovich for your very quick responses.
All very helpful and spot on!
Padjmaja,
Using your logic above, how would you create a sum for a new column called [Number of Systems]?
I would only want the Number of Systems counted for the Max(Date) by JobID.
Appreciate your insight and experience.
Klaus