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 :)
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).
Thank you Cape Cod Gunny for your respond,
However is there an alternative way to achieve this besides using a union?
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