question

Dave Morrison avatar image
Dave Morrison asked

MDX - Rank Data on Specific dates data

Hi all, So I have a scenario where I need to produce a dataset for a range of dates. Lets say that there is one value per financial quarter, going back 4 quarters from a passed in financial quarter. So I have product, financial year & quarter and value. Each product will have 4 records, one for each financial quarter. What I need to do is sort the products by the value of the passed in financial quarter (as above). So if Prod A has a value of 22 in Q2 and a value of 5 in Q4 (the passed in quarter) and Prod B has 6 in Q4 and 0 in Q2 I want Prod B to sort above Prod A as its passed in Q4 value is higher. Basically I need to ignore the value for all quarters other than the passed in quarter for sorting. All input appreciated Thanks Dave
ssasmdx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Dave Morrison avatar image
Dave Morrison answered
Just as a note to anyone that might be having the same issue, I solved this by created a calculated member in the query that was scoped to the specific date I needed. I then applied RANK() over this and this resolved my issue
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.