Hi all,
To get a better comparison of my measures, I would like to display the measures above the (user) dimension in the columns. Is there a possibility? I was able to create the right order with calculated columns, but then my dynamic date on the user dimension no longer works.
Thank you very much / Marlene
Jelle van Hulten Hi Marlene,
I managed to create below crosstab, I think this is what you are looking for? 
In order to create this crosstab, the first thing you should do is add two columns to your date table / dimension, which we use later on for dynamic captions in TARGIT. These columns should contain LastYear and NextYear. So for example:
After you have done that create the crosstab in TARGIT whilst following the steps below.
1. Add row dimension and measures.
2. Add user dimension (ThisMonth & ThisMonth_LY)
Your crosstab should then look something like this:
3. Add calculated measure Revenue_TY = sum(d1, 0, m1, 0)
4. Add calculated measure Revenue_LY = sum(d2, 0, m1, 0)
5. Add calculated measure Budget_TY = sum(d1, 0, m2, 0)
6. Add calculated measured Budget_LY = sum(d2, 0, m2, 0)
Now your crosstab should look something like this:
7. Rename your user dimensions. ThisMonth to Revenue and ThisMonth_LY to Budget.
8. Hide measures 'Revenue' and 'Budget'.
9. Hide measure 'Budget_TY' in user dimensions 'Revenue'. Go to Properties > Visibility and select 'Hide for a range of .....'. After that select 'Specific element' and select the measure 'Budget_TY' in the crosstab. Finish by selecting 'Apply'.
10. Hide measure 'Budget_LY' in user dimensions 'Revenue'. Go to Properties > Visibility and select 'Hide for a range of .....'. After that select 'Specific element' and select the measure 'Budget_LY' in the crosstab. Finish by selecting 'Apply'.
11. Hide measure 'Revenue_TY' in user dimensions 'Budget'. Go to Properties > Visibility and select 'Hide for a range of .....'. After that select 'Specific element' and select the measure 'Revenue_TY' in the crosstab. Finish by selecting 'Apply'.
12. Hide measure 'Revenue_LY' in user dimensions 'Budget'. Go to Properties > Visibility and select 'Hide for a range of .....'. After that select 'Specific element' and select the measure 'Revenue_LY' in the crosstab. Finish by selecting 'Apply'.
When you have done that, your crosstab should look something like this:
13. Edit dynamic caption for 'Revenue_TY' and use something like the follow caption: {Date Month:first}-{Date Year:first}
14. Edit dynamic caption for 'Revenue_LY' and use something like the follow caption: {Date Month:first}-{Date LastYear:first}
15. Edit dynamic caption for 'Budget_TY' and use something like the follow caption: {Date Month:first}-{Date NextYear:first}
16. Edit dynamic caption for 'Budget_LY' and use something like the follow caption: {Date Month:first}-{Date Year:first}
After that your crosstab is all done!
Lastly, if you would like to have your Revenue and Budget more centered, I suppose you could add textboxes and group those together with the crosstab after you have colored the original column names white/blank.
Hope this is what you were looking for.
Best regards,
Jelle
Marlene Knaup Hi Jelle,
Many thanks for the detailed tutorial. :)
I will check if there is a way to include LastYear and NextYear as an extra attribut.
Unfortunately, I would have to have this not only for years, but also for months, weeks and fiscal periods so I hope TARGTI could implement a better solution for this.
I will therefore create a feature request to obtain this table structure with the measures above dimensions.
Comments
2 comments