Skip to Content
author's profile photo Former Member
Former Member

Total Sales = Sales Unit x Sales Price in same application

Hi experts,

I have read and tried some of the script logic in the forum but I'm still not able to get the results I want.

I have a application with the following dimensions: account,category,time,entity,datasrc,bussegment,inputcurrency,scd,vcd.

The same price apply to all combination of scd and vcd, the units will vary depending on the scd and vcd combination.

For example, when records are posted, the records in DB as follows:

Salesprice:

Account | Category | Time | Entity | Datasrc | Inputcurrency | SCD | VCD |

sprice | budget |2006.jan | HK | input | usd | sdump | vdump | $10

Salesunits:

Account | Category | Time | Entity | Datasrc | Inputcurrency | SCD | VCD |

sunits | budget | 2006.jan | HK | input | usd | aax | aan | 50 |

sunits | budget | 2006.jan | HK | input | usd | coc | cex | 100 |

TotalSales should be:

Account | Category | Time | Entity | Datasrc | Inputcurrency | SCD | VCD |

tsales | budget | 2006.jan | HK | input | usd | aax | aan | $500 |

tsales | budget | 2006.jan | HK | input | usd | coc | cex | $1000 |

Have tried using script logic:

[#tsales]=sunits * [scd].[sdump],[vcd].[vdump],[account].[sprice]. For this syntax, total sales will only be updated when salesunits is updated. The total sales remain unchange when there's a change in price.

Please advice on how should the price be retrieved?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 18, 2008 at 05:16 AM

    Assuming you're on BPC 5.1 for MS, I'd recommend using SQL syntax not MDX.

    Also, I'd recommend using InputCurrency as the dimension name in your rate app, not your reporting app. That's not critical, though. What is a little strange is that you're doing this data manipulation in USD, not LC. That's up to you, though.

    // we take entity, category, time, SCD and VCD from the user's data submission
    // entity, category & time require no shift of data
    // but since we want to re-calc all SCD and VCD when the price changes,
    // we need to pick up ALL members of these two dims
    *xdim_memberset SCD = <ALL>
    *xdim_memberset VCD = <ALL>
    
    // fix other dims; this is optional
    *xdim_member datasrc = input
    *xdim_member inputCurrency = USD
    
    // this is optional, but may help performance - but ONLY IF this is the only logic in your commit block.
    *xdim_member account = SalesUnits
    
    
    *lookup
    *dim MyPrice:Account="SalesPrice"
    *dim scd="sdump"
    *dim vcd="vdump"
    *endlookup
    
    *when *
    *is *
    *REC(factor=lookup(MyPrice),account="TotalSales")
    *endwhen
    

    an alternate way of handling the when/if, if you want to be really, really belt & suspenders about it:

    *when VCD
    *is not VDUMP
    *when SCD
    *is not SDUMP
    *REC(factor=lookup(MyPrice),account="TotalSales")
    *endwhen
    *endwhen
    

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 18, 2008 at 05:18 AM

    Apologies for the double-postings -- I'm getting some timeout problems the last few days, when posting to SDN.

    Edited by: Tim Klem on Dec 18, 2008 1:22 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 18, 2008 at 07:13 AM

    Tim,

    Thank you so much! Your logic is working.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      You're welcome. Actually now that I think of it, the cleanest approach is to use XDIM_NOSCAN, rather than the belt & suspenders.

      But if you have 1000's of members in either of these dimensions, you may want to consider alternate approaches.

      // we take entity, category, time, SCD and VCD from the user's data submission
      // entity, category & time require no shift of data
      // but since we want to re-calc all SCD and VCD when the price changes,
      // we need to pick up ALL members of these two dims
      *xdim_memberset SCD = <ALL>
      *xdim_memberset VCD = <ALL>
      
      *xdim_noscan SCD = SDUMP
      *xdim_noscan VCD = VDUMP
       
      // fix other dims; this is optional
      *xdim_member datasrc = input
      *xdim_member inputCurrency = USD
       
      // this is optional, but may help performance - but ONLY IF this is the only logic in your commit block.
      *xdim_member account = SalesUnits
       
       
      *lookup
      *dim MyPrice:Account="SalesPrice"
      *dim scd="sdump"
      *dim vcd="vdump"
      *endlookup
       
      *when *
      *is *
      *REC(factor=lookup(MyPrice),account="TotalSales")
      *endwhen
      

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.