Welcome Guest! To enable all features please Login or Register.
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: 12

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

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

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

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,

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

Ole Dyring
Users browsing this topic
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.



We use cookies to improve your site experience, but they also provide us with information on your use of our website.
To find out more about the cookies we use and how to delete them, see our Privacy Policy. By continuing to browse the site, you are consenting to our use of cookies.