en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Lenka Eriksen  
#1 Posted : Friday, January 13, 2017 8:24:43 AM(UTC)
Lenka Eriksen
Rank: Member

Groups: extranet\Forum
Joined: 1/11/2017(UTC)
Posts: 7

Hi,

I would like to ask for help with my calculation.

I have the following crosstab:
X axis shows Posting Month
Y axis has 2 dimensions: 1. Country/Customer hierarchy (from cube) and 2. Posting Year

UserPostedImage

I would like to add Index calculation to Y axis for each Country/Customer hierarchy as a calculation of 2 values of Posting Year dimension.

My problem already starts when i add calculaion as new measure, i don't know how to chose that it should be new measure on Country/Customer hierarchy dimension, not also for each Posting Year.

Please help :-)

Lenka
Ole  
#2 Posted : Friday, January 13, 2017 10:07:56 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 356

Hi Lenka,

I am afraid there is no perfect way to do this as you will need to use calculations "As a new measure" - and TARGIT will always insert this new measure for each distinct dimension member.

However, you can achieve something like this:

UserPostedImage

The original crosstab looked like this:

UserPostedImage

This is how I have done it:

I added three calculations "As a new measure":

  • Last Year = sum(0, d1(s), m1)
    This Year = sum(0, d2(s), m1)
    Diff = sum(0, d2(s), m1) - sum(0, d1(s), m1) (you will need a different index calculation here)


I enabled the "Measures down" option - from the Properties tab.

I added a visibility agent to hide one of the years: Hide member, allcount(0, d1:0(s), m1) = 1

I worked with visibility options to hide:

  • Grand Totals
    Subtotals
    Original measure (No of Sales in my example)
    Year labels



I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Lenka Eriksen  
#3 Posted : Monday, January 16, 2017 11:34:26 AM(UTC)
Lenka Eriksen
Rank: Member

Groups: extranet\Forum
Joined: 1/11/2017(UTC)
Posts: 7

Thank you very much Ole, it's exactly what I needed :-)
Lenka Eriksen  
#4 Posted : Monday, January 16, 2017 12:46:18 PM(UTC)
Lenka Eriksen
Rank: Member

Groups: extranet\Forum
Joined: 1/11/2017(UTC)
Posts: 7

Ups, ran into a problem:

I thought "Last Year = sum(0, d1(s), m1)" would be referencing 2016 posting year specificaly, but it looks like it's just taking "the first".
So when a market/customer is new and there is no sales amount for 2016, then 2017 sales amount becomes d1(s) = last year.

Is there a way to make sure that d1(s) is always 2016 (or actually dynamic year-1)?

Thank you!
Ole  
#5 Posted : Wednesday, February 8, 2017 8:55:10 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 356

Hi Lenka,

In the case where one year is missing, you may try to enable 'Empty members'. Right click the crosstab and go into the Data section to enable 'Empty members'. (Notice that this option is only available from the 2017 version and onwards).

Now, this may solve your problem already.

If it produces too much data (eg. also showing empty countries where it shouldn't), you may need to add a visibility agent as well:

'Hide member', with this condition: count(all, 0, m1) = 0 and count(all, 0(l(1,0)), m1) = 0

Best regards,
Ole
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error