Displaying Top 5 Products for selected quarter and same quarter for prior year

[BubuReloaded]

Hi All,

Please kindly assist assist, i'm trying to display the top 5 products for selected quarter (2019 Q2) and same selected quarter for prior year (2018 Q2)

On the the Categories of my column chart i have current year selected quarter and prior year of the selected quarter, on the Series I have the products.

I tried using a rank, TopCount function, but I can't seem to think of a way to sort both the current and previous at the same time.

Is there a way i can filter on multiple ranks e.g. Rank Current<=5 AND Rank Prior Year<=5 in a query.

Thank you in advance for your assistance :)

[Cape Cod Gunny]

It sounds like you need to have three separate queries.

1. Query #1 TOP 5  Products from 2019 Q2.
2. Query #2 TOP 5  Products from 2018 Q2.

Union these two Product queries.

3. Query #3 Product Details where Product in (Union).

[BubuReloaded]

Thank you Cape Cod Gunny for your respond,

However is there an alternative way to achieve this besides using a union?

[oscarca]

If you are using a cube you could:

Create 4 data items:

[Sales 2018] = tuple([Sales],[2018)
[Sales 2019] = tuple([Sales],[2019)

generate ( [Products] , topCount (  [Products] , 5 , [Sales 2018 ) )
generate ( [Products] , topCount (  [Products] , 5 , [Sales 2019 ) )

Best regards,
Oscar