en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Morten Søndergaard  
#1 Posted : Wednesday, August 29, 2012 1:27:04 PM(UTC)
Morten Søndergaard
Rank: Advanced Member

Groups: extranet\Forum
Joined: 6/20/2012(UTC)
Posts: 10

I am creating a report comparing item-minimum stock with actual sales last month. Like this:

ITEM | MINIMUMSTOCK |SalesLastMonth | Difference
(Dim) |(DIM) | (Measure) |(Calculation)

Item 1 |500 |800 |-300
Item 2 |200 |300 |+100
Item 3 |600 |500 |-100

Is it possible to do the calculation in the last column? My problem is that the calculation has to calculate the difference between a dimension value and a measure.

How would you solve this?

Best regards

Morten Søndergaard

Edited by user Wednesday, August 29, 2012 1:54:32 PM(UTC)  | Reason: Not specified

Ole  
#2 Posted : Thursday, August 30, 2012 12:40:25 PM(UTC)
Ole
Rank: Administration

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

Hi Morten,

If your table is as simple as the one described, then you can derive numeric values from dimension values in this way:

if allcount(d1, d1:0, m1) = allcount(d1, d1:@"[Item 1].[500]", m1) then 500 else
if allcount(d1, d1:0, m1) = allcount(d1, d1:@"[Item 2].[200]", m1) then 200 else
if allcount(d1, d1:0, m1) = allcount(d1, d1:@"[Item 3].[600]", m1) then 600 else 0

If put this into a separate (and possibly hidden) column you can then use these vales for your difference calculation.

But I got a feeling that your dimension is more complex with a large number of combinations for ITEM and MINIMUMSTOCK. If this is the case, I think it would be easier to create it as a real measure in your Data Warehouse.

Best Regards,
Ole

Edited by user Thursday, August 30, 2012 12:43:04 PM(UTC)  | Reason: Not specified

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