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

Total value of a particular dimension using script logic

Hi experts,

can anyone tell me whether script logic is able to compute the total of a specify dimension? how is it being done?

For example, I have the following in DB:

Account | Category | Entity| Time | Tcode| Inputcurrency |

Vol1 | Budget | LocA | 2005.Feb | A | LC | $20

Vol1 | Budget | LocA | 2005.Feb | B | LC | $50

Vol1 | Budget | LocA | 2005.Feb | C | LC | $70

Vol1 | Budget | LocB | 2005.Feb | A | LC | $30

Vol1 | Budget | LocB } 2005.Feb | B | LC | $90

I need to compute total vol by entity, so the results should

total vol for LocA = $20 + $50 + $70 = $140

total vol for LocB = $30 + $90 = $120

Then this total vol will be use for another computation.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2008 at 10:54 AM

    Hi,

    If you have put this script in default logic, it may not work, because only the entity you have send data will be part of the scope. You may have to add all entities by redefining the scope with an *XDIM_MEMBERSET or execute your script from the datamanager and select all entities.

    In addition to that, the following two lines containt a #tot which is not defined anywhere ==> they won't do anything.

    - LOOKUP(#tot)

    - *rec(location=#tot)

    Regards,

    Marcel

    Add a comment
    10|10000 characters needed characters exceeded

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

    Hi Puya,

    By reporting on the higher members of the all dimensions, except entity, you can compute total volume by entity.

    In you example, say we have D as parent of A,B,C. So

    Vol1 | Budget | LocA | 2005.Feb | D | LC will be $140

    Kranthi

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 22, 2008 at 08:33 AM

    Hi,

    You could try the following code to calculate your product total on a memory variable "#TOT" and then re-use it later in in the code

    *WHEN Prod

    *IS *

    *REC(Prod="#TOT")

    *ENDWHEN

    *GO

    *WHEN Prod

    *IS "#TOT"

    ...

    *ENDWHEN

    Second option would be to use the *CALC_DUMMY_ORG instruction so that the system will calculated all the parents of the dimension you specifiy and you can then access these parent members by adding a "#" in front of the member name. This will only work for one dimension in a *COMMIT section

    Regards,

    Marcel

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2008 at 08:47 AM

    Hi Marcel,

    attached my logic:

    *when account

    *is "vol1"

    *when entity

    *is *

    *rec(entity=#ltotal)

    *endwhen

    *endwhen

    *go

    *when account

    *is "vol1"

    *when entity

    *is #ltotal

    *rec(expression=%value% + lookup(#tot), entity="totloc", trade="NA")

    *rec(location=#tot)

    *endwhen

    *endwhen

    *commit

    I post the following records

    ACCOUNT,CATEGORY,DATASRC,ENTITY,TIME,INPUTCURRENCY,TRADE,SIGNEDDATA

    VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,ASED,-2.0000000

    VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,TPED,-5.0000000

    I get the records with the correct result:

    ACCOUNT,CATEGORY,DATASRC,ENTITY,TIME,INPUTCURRENCY,TRADE,SIGNEDDATA

    VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,NA,TOTLOC,-7.0000000

    However, when I change the value of the first record as follows:

    ACCOUNT,CATEGORY,DATASRC,ENTITY,TIME,INPUTCURRENCY,TRADE,SIGNEDDATA

    VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,ASED,3.0000000

    VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,TPED,-5.0000000

    the result is wrong:

    ACCOUNT,CATEGORY,DATASRC,ENTITY,TIME,INPUTCURRENCY,TRADE,SIGNEDDATA

    VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,NA,TOTLOC,-10.0000000

    The total should be 8 not 10.

    So I try to add in *clear_destination:

    *when account

    *is "vol1"

    *when entity

    *is *

    *rec(entity=#ltotal)

    *endwhen

    *endwhen

    *go

    *when account

    *is "vol1"

    *when entity

    *is #ltotal

    *rec(expression=%value% + lookup(#tot), entity="totloc", trade="NA")

    *rec(location=#tot)

    *endwhen

    *endwhen

    *clear_destination

    *destination entity="totloc"

    *commit

    This results in:

    ACCOUNT,CATEGORY,DATASRC,ENTITY,TIME,INPUTCURRENCY,TRADE,SIGNEDDATA

    VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,ASED,0.0000000

    VOL1,BUDGET,INPUT,TOTLOC,2005.JAN,USD,NA,-12.0000000

    VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,TPED,0.0000000

    Instead of clearing the amount in TOTLOC, it clears the value I have send and the total is still incorrect.

    May I know what's wrong with my logic?Appreciate your advise.

    Add a comment
    10|10000 characters needed characters exceeded

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.