Use of Max() in Dimension AND Expression

[klaus_feldam]

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

[Anonymous · Accepted solution]

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.

[#3]

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))

[simenkg]

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

[klaus_feldam]

Thank you Padmaja Gade, Simen Kind Gulbrandsen and Michael Solomovich for your very quick responses.

All very helpful and spot on!

[klaus_feldam]

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