en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Anmol Sunsoa  
#1 Posted : Thursday, September 8, 2016 2:32:19 PM(UTC)
Anmol Sunsoa
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/5/2016(UTC)
Posts: 10

Hello Experts

I have tried to best explain in an excel layout but basically for each shop can
We show the Dynamic date with the Actual|vs RF1|vsLY

I am trying to do in TargIT but I am getting lost.
Can you tell me if I am even close?

I have created a Cross tab with the following structure
Comparison: Periods ( Year, Month, Week)derived from dynamic dates
Dimension :Dynamic Date: This Year, Last Year for each period
Dimension: Store Name
2 Measures Sales V and Sale Budget

I want to show 3 Measures for each period
1. Actual
2. Actual vs Budget
3. Actual vs Last Year

At present I am creating a Measure for each period

Actuals
Week Actual: sum(@"[Week]";"[Week To Date This Year]", 0, m1)
Month Actual: sum(@"[Month]";"[Month To Date This Year]", 0, m1)
Year Actual: sum(@"[Year]";"[Year To Date This Year]", 0, m1)

RF1
Week RF1: sum(@"[Week]";"[Week To Date This Year]", 0, m1) / sum(@"[Week]";"[Week To Date This Year]", 0, m2) - 1
The same for Month and Year

My main issue is Actual vs LY as Sales for This year are d-2 and Sales for LY d-1
So for each period sum(d-2, 0, m1) / sum(d-1, 0, m1)
I don't know hot to reference the d-1 and d-2 in the custom hierarchy.

Kind Regards

Anmol Sunsoa

Edited by user Thursday, September 8, 2016 2:51:54 PM(UTC)  | Reason: Add a picture

Ole  
#2 Posted : Friday, September 9, 2016 11:36:10 AM(UTC)
Ole
Rank: Administration

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

Not quite sure I understand your challenge without seeing a screenshot.

It seems like your problem is due to having two comparisons on the horizontal axis of your crosstab. This will create a custom 2 x 3 hierarchy that is difficult to refer to calculations wise.

Couldn't you make it work with just one comparison with six elements? Last Year, Last Year This Month, Last Year This Week, This Year, This Year This Month, This Year This Week.

That would make your reference syntax a lot easier to manage.

BR / 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
Anmol Sunsoa  
#3 Posted : Monday, September 12, 2016 9:32:32 AM(UTC)
Anmol Sunsoa
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/5/2016(UTC)
Posts: 10

Hello Ole,

I only have 1 comparison which has members each of which has elements for this year last year.
Week
Month
Year
Last 13 Weeks

Below that I have the Dynamic dates so Week to Date This, Week to Date Last Year etc
The dimensions I have store name.

What I need to show in one cross tab
is for each of the comparison members show actual, vs budget, vs last year as measures.

Does that make sense.

Kind Regards

Anmol Sunsoa
Ole  
#4 Posted : Monday, September 12, 2016 9:40:51 AM(UTC)
Ole
Rank: Administration

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

Here is how you can add screenshots to your posts: https://www.targit.com/e...?g=posts&t=66#post91
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
Anmol Sunsoa  
#5 Posted : Monday, September 12, 2016 12:25:23 PM(UTC)
Anmol Sunsoa
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/5/2016(UTC)
Posts: 10

Hello Ole,

Thanks for helping me out with the URL.
UserPostedImage

I am getting close as you can see from the 1st crosstab for Week.
Obviously you will have a better idea.

However in my scenario I have to hide the month measures for the Week Comparison
and the week measures for the Month comparison. It is very confusing when working with ranges.

Kind Regards

Anmol Sunsoa

Anmol Sunsoa  
#6 Posted : Tuesday, September 13, 2016 8:33:53 AM(UTC)
Anmol Sunsoa
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/5/2016(UTC)
Posts: 10

Hello Ole,

I tried it another way as I will keep trying but I am at a loss.

So 1 Comparison with Date elements where each as TY LY
Week
Month
Year
Last 13 Weeks

Then I created 3 more comparisons to act as place holders for my 3 measures
Actual
vs RF1
vs LY

I finally created a custom calculation as measure
Actual: if count(0, d1:0, m1) = 1 then sum(0, 0, m1) else if count(0, d1:0, m1) = 2 then sum(0, 0, m2) else 0

vs RF1:
if count(0, d1:0, m1) = 2 then sum(0, 0, m1) / sum(0, 0, m2) - 1 else 0

UserPostedImage

I basically want the same as this but in 1 cross tab, is it at all possible

UserPostedImage

Kind Regards

Anmol Sunsoa



Ole  
#7 Posted : Tuesday, September 20, 2016 10:48:24 AM(UTC)
Ole
Rank: Administration

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

Hi Anmol,

I still think you can do it by simplifying your comparisons.

In this example I am using just one comparison with 8 comparisons elements: Month TY and LY, Quarter TY and LY, Year TY and LY, Last 3 Months TY and LY. This should more or less resemble your requirements.

I have two measures: Revenue (m1) and Budget (m2).

The first screenshot shows the crosstab without any further visibility/calculations options:

UserPostedImage

In the next screenshot I have added two calculations, both 'As a new measure':

  • Actual vs Budget: (sum(0, 0, m1) - sum(0, 0, m2))/ sum(0, 0, m2)
    Actual TY vs LY: (sum(0, 0, m1) - sum(-1, 0, m1))/ sum(-1, 0, m1)

Also, I have used the Visibility formatting option to hide Budget and to hide a range of my comparison. Actually I used the 'Hide specific element' option to hide all four LY comparison elements one by one.

UserPostedImage

BR / 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
Anmol Sunsoa  
#8 Posted : Wednesday, September 21, 2016 8:05:18 AM(UTC)
Anmol Sunsoa
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/5/2016(UTC)
Posts: 10

Hello Ole,

Thanks for the great solution.
I get lost at the hide specific element so please can you add some further screenshots.
I have to explain to our power users how to use visibility effectively so can you provide any material around this topic?

Kind Regards

Anmol Sunsoa
Anmol Sunsoa  
#9 Posted : Wednesday, September 21, 2016 10:10:04 AM(UTC)
Anmol Sunsoa
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/5/2016(UTC)
Posts: 10

Hello Ole,

I am getting close :-)

The 1st Cross tab is using your work around (worked out how to hide LY)
The 2nd is to sense check figures
The 3rd is how I did it splitting each dynamic date into a cross tab.

When use sum(-1,0,m1) I get undefined for Week and numbers are out of sync.
This was my main issue how to work out this year vs Last year

UserPostedImage

Kind Regards

Anmol Sunsoa

Edited by user Wednesday, September 21, 2016 10:12:52 AM(UTC)  | Reason: Not specified

Anmol Sunsoa  
#10 Posted : Wednesday, September 21, 2016 10:55:56 AM(UTC)
Anmol Sunsoa
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/5/2016(UTC)
Posts: 10

Hello Ole,

Thanks for your assistance

UserPostedImage

I have almost got there :-)
Is there anyway I can put a border around each comparison?

Kind Regards

Anmol Sunsoa

Edited by user Wednesday, September 21, 2016 10:56:35 AM(UTC)  | Reason: Not specified

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