POP not working with Time Hierarchy and additional Dimension

#1

Hi everyone,

New to the Dundas world and hoping your vast amount of experience will be able to assist me.

I am trying to create a simple flat table, using POP to show the previous months data (up to 6 months) which will allow me to create a combination measure.

Quite simply I have a time hierarchy (Month/Year) and an associated naming convention to identify the period in question i.e.

Month/Year Period Ref.
0120 0320
0220 0420…
1120 1320
1220 1420
0121 1520…
0421 1720
0521 0121…etc.

There is a simple table which shows the relation between these, and in addition a second table that sums the value I am trying to show with the relative period name.

If I place just the Month/Year time hierarchy into rows, the POP works fine, however as soon as I drag the Period Ref. also into rows, the POP value disappears.

Essentially the ultimate aim, is to have a dashboard, where a user selects a period ref. from a Value Drop down box, which in turn will bring back a summary of the related current month and previous 5 months.

I was able to achieve this in the past where I converted the Period ref. into a date, however due to an extended financial year this no longer works as 1320 for example cannot be converted to a date.

Any assistance on this would be really appreciated.

Thanks:smiley:

#2

Hi Chris,

Welcome to the Dundas World!

Would you be able to give a little insight into the data itself. I’m assuming 0120 is the MMYY format that you’ve been using, and thus having 1320 would not work. Is this a correct assumption?

Furthermore, is your intention to have 1320 correspond with the date of 0121?

Would you be able to provide a screen capture to what you had done, as well as the current orientation of the dashboard? I think this would help visualize what is trying to be done.

Thank you in advance,

#3

Hi Matthew.

Essentially I have 2 tables, and I have built a very simple cube.
Table 1 is as above, with the Period ref. and MMyy data (both strings)
Table 2 is a list of contracts associated with a period ref, and the values I want to work with.
i.e. Period Contract Value
0120 123456 400
0120 789112 300
0220 123456 200
0220 789112 100 etc.

There is a join between the 2 tables based on the period ref. to essentially show a relation between the date and the reference. Interestingly, if I bring in the Contract (D) Table 2 and MMyy (H) Table 1 it works, but I then when I bring in the Period Ref. from either table the POP stops.

I have attached a couple of screen shots which may help.

Thanks

With%20Period%20Ref
With Period Ref.png1920×772 43.1 KB
Without%20Period%20Ref
Without Period Ref.png1920×1080 59.2 KB

#4

Hi Chris

I believe I know what’s going on, so the dimension you are adding is a string. After you add that value I don’t think there is a corresponding measure containing the RP, contact, and period from a previous time period. So I believe that the P.o.P would show up as empty when you add that additional dimension in since there is no corresponding value from a previous period containing all those dimensions.

Please let me know if this helps.

Thanks,

Matt

#5

Hi Chris,

I wanted to quickly follow up with you, did this help answer your inquiry?

#6

Hi Matt,

Thanks for the update - I think I know what you are referring to, however I am struggling to determine what I would need to do to resolve this. Do I need to update the source data with relevant dates as well as the Related Period that is in each row of data.

Or could it be a bit more complex, such as creating a cube first that merges the data together to give a corresponding string and measure and date, and then adding this cube to a final cube that will be able to process this?

Thanks

Chris

#7

Hi Chris,

If you want the users to filter the data table by Period hierarchy, you can add it as a slicer rather than adding it to the row under rows. You can then add a value filter and connect it to the Period hierarchy in the slicer. This way, the data will not be grouped by the Period hierarchy and the user will still be able to filter the data by Period.

Besides, if you want to show the Period column in the data table, then you can go to the underlining data cube, create a copy of the Period column using the copy element transform. Then you can click on the process results and then switch the newly created copy of Period Hierarchy to a measure. Then add this copy measure in the data table as a measure. This way you can show the period column. Your filter will still be connected to the original period hierarchy added as the slicer.

I hope this helps. Please let me know in case you have any questions about the above approach.

Regards,
Pankaj

#8

Thanks everyone for your advice. I decided in the end to take out the need to report via the Period Hierarchy reference and just use the months as suggested and this seems to have done the job.
Really appreciate everyone who replied.