en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Justin Haber  
#1 Posted : Tuesday, March 24, 2015 10:27:13 AM(UTC)
Justin Haber
Rank: Member

Groups: extranet\Forum
Joined: 2/6/2015(UTC)
Posts: 7

Hi,

I have a list of items, which have some additional dimensions on the horizontal axis of my crosstab. Within the measures (of which there are 3), I have added an additional calculation for all dimension values, which is called Weeks Cover (calculated by dividing Stock by Sales).

Using visibility agents, I have then created views splitting the weeks cover into groups (e.g. 40+ weeks, 20-30 weeks, 5-10 weeks etc). Each of these views shows all the Items numbers (together with the additional item dimensions) and all the measures (including my calculated weeks cover). Whilst this gives me the individual SKU detail, I am looking to have a rolled up version and am struggling to do the following:

1. Each SKU belongs to a brand and I want to see the total value of stock (one of my measures for each Item) for each brand (this would be for each of my views where weeks cover is already grouped together). Ideally I'd like also to hide the individual component SKUs of each brand, as this is already visible on my existing views.

2. I'd like to have a single summary view where I can see a line each of the weeks cover in groups, detailing the value of stock and the count of how many SKUs are in each weeks cover group. I have tried to do this on my master SKU list (which doesn't break out into groups) by using comparisons and custom calculations but to no avail.

Any help appreciated.
Ole  
#2 Posted : Monday, March 30, 2015 9:03:31 AM(UTC)
Ole
Rank: Administration

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

Hi Justin,

I would be happy to have a go on this one, but I have trouble understanding your exact requirements just by reading your post.

As in many other cases, a picture will tell more than words. I would appreciate if you could upload some images with further explanation on what you have already done, and what you want to achieve.

You can see here how to add images to your post: http://www.targit.com/en...?g=posts&t=66#post91

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
Justin Haber  
#3 Posted : Tuesday, April 14, 2015 2:10:37 PM(UTC)
Justin Haber
Rank: Member

Groups: extranet\Forum
Joined: 2/6/2015(UTC)
Posts: 7

Hi Ole,

The table below shows where I have been able to create a view for items showing the number of weeks cover by Item (calculated as Free Stock Qty / Sales Qty) where I have been able to split into individual views the different weeks cover groups (e.g. 50+ Weeks, 40 - 50 Weeks, 30 - 40 Weeks, 5 - 10 Weeks etc.) - the example below is for cover weeks 50+

UserPostedImage

What I am wanting to do is to show this at Brand level instead of at individual Item level, and if possible to drill down to show the individual SKUs that belong to that Brand. I have been able to recreate this in excel as below:

UserPostedImage

You can also see from the below Item Dimension exactly how the relationship between Brand and Item Number exists.

UserPostedImage

Finally I’d like to see a summary view, where in a single view I can see a single line per Cover Group, where it shows the Number of Items in the Cover Group and the value of that Stock, as below

UserPostedImage

I have tried using comparisons and custom calculations but with no luck - I hope this gives you a clearer picture of what I’m trying to achieve
Ole  
#4 Posted : Monday, April 20, 2015 11:57:53 AM(UTC)
Ole
Rank: Administration

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

Hi Justin,

The 1st part:
From the screenshot of your Item Dimension it does seem that it already contains the required hierarchy to show your data at Brand level and at the same time being able to drill down to the individual SKUs. Essentially, just copy the crosstab you already got and replace the dimension(s) on the vertical axis with your “Item, Brand hierarchy” dimension.

The 2nd part:
You should be able to do this with a combination of comparisons and calculations:
1. Create an initial crosstab with Stock Value as measure and Item Numbers along the horizontal axis. This will probably produce a very wide crosstab.
2. Add a comparison with comparison elements and criteria to produce your week groups. Make sure the comparison goes on the vertical axis.
3. Add a calculated column for Count of Item Numbers: count(all, 0, m1)
4. Add a calculated column for Sum of Free Stock Value: sum(all, 0, m1)
5. Use “Properties / Visibility” to hide the initial Stock Value measure.

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
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