CODEWITHSUNDEEP
ssasmdx
Kire
Kire

Reputation: 31

MDX: How to get the previous period based on top count?

I must calculate the top 10 brands for this year and then get the same figures for last year... but I found it a little bit tricky.

I have the following set for the top 10 brands over year to date measure.

My date is a parameter (Month-Year) and everything is ok so far.

filter(TopCount(
              ([10 - Brand].[Brand].Allmembers) * {STRTOMEMBER(@DATE)}
          ,10,
         [Measures].[Act YTD]
          ), [Measures].[Act YTD] <> null)

But now I have to use this set and get the previous year.

The expected results is get the top 10 this year and after, last year figures in the same query:

Year | Quarter |   Month   | Brand | Act YTD
2019 | 20193   |  Aug 2019 | BR01  | 150
2019 | 20193   |  Aug 2019 | BR02  | 250
2019 | 20193   |  Aug 2019 | BR03  | 350
2019 | 20193   |  Aug 2019 | BR04  | 450
2019 | 20193   |  Aug 2019 | BR05  | 550
2019 | 20193   |  Aug 2019 | BR06  | 650
2019 | 20193   |  Aug 2019 | BR07  | 750
2019 | 20193   |  Aug 2019 | BR08  | 850
2019 | 20193   |  Aug 2019 | BR09  | 950
2019 | 20193   |  Aug 2019 | BR10  | 1150

2018 | 20183   |  Aug 2018 | BR01  | 100
2018 | 20183   |  Aug 2018 | BR02  | 200
2018 | 20183   |  Aug 2018 | BR03  | 300
2018 | 20183   |  Aug 2018 | BR04  | 400
2018 | 20183   |  Aug 2018 | BR05  | 500
2018 | 20183   |  Aug 2018 | BR06  | 600
2018 | 20183   |  Aug 2018 | BR07  | 700
2018 | 20183   |  Aug 2018 | BR08  | 800
2018 | 20183   |  Aug 2018 | BR09  | 900
2018 | 20183   |  Aug 2018 | BR10  | 1000

I need guidance :)

Upvotes: 2

Views: 56

Answers (2)

Kire
Kire

Reputation: 31

I found the solution.

Instead of displaying the results in rows, now I am displaying them as columns.

And I had to redesign the report due to row groupings and column groupings.

This way I can create the top 10 set based on this year's date and get the Y-1 measures.

Year | Quarter |   Month   | Brand | Act YTD | Act YTD -1 | 
2019 | 20193   |  Aug 2019 | BR01  | 150     | 100        | 
2019 | 20193   |  Aug 2019 | BR02  | 250     | 200        | 
2019 | 20193   |  Aug 2019 | BR03  | 350     | 300        | 
2019 | 20193   |  Aug 2019 | BR04  | 450     | 400        | 
2019 | 20193   |  Aug 2019 | BR05  | 550     | 500        | 
2019 | 20193   |  Aug 2019 | BR06  | 650     | 600        | 
2019 | 20193   |  Aug 2019 | BR07  | 750     | 700        | 
2019 | 20193   |  Aug 2019 | BR08  | 850     | 800        | 
2019 | 20193   |  Aug 2019 | BR09  | 950     | 900        | 
2019 | 20193   |  Aug 2019 | BR10  | 1150    | 1000       |

Upvotes: 1

vldmrrdjcc
vldmrrdjcc

Reputation: 2112

You could try to use Union and Parallelperiod functions, something like this, depending on the name of you date dimension, hierarchy and level:

Union(filter(TopCount(
              ([10 - Brand].[Brand].Allmembers) * {STRTOMEMBER(@DATE)}
          ,10,
         [Measures].[Act YTD]
          ), [Measures].[Act YTD] <> null)
,
filter(TopCount(
              ([10 - Brand].[Brand].Allmembers) * {PARALLELPERIOD([Date].[Calendar Year].[Calendar Year], 1, STRTOMEMBER(@DATE))}
          ,10,
         [Measures].[Act YTD]
          ), [Measures].[Act YTD] <> null))

Upvotes: 0

Related Questions