Rule to work out monthly average exchange rate

    Rule to work out monthly average exchange rate

    All,

    Please find below my discussion with Holger in regards to how to work out average exchange rate in Palo using rule. Hope this helps any of you who are having similar issues :)

    Initial message from me:

    Hi Holger,

    I was wondering if you could help us with rule in Palo.

    What we are working on at the moment is we want to create a FX cube which will store the foreign exchange rate each day, with additional monthly, half yearly and annual average rate. To do this, we have created a cube called FX Cube where the dimensions (at this stage) are Day (which consists of dates (1/7/09, 2/7/09, ..., 30/6/15) as well as the consolidation element for each month (July 2009, August 2009, ..., June 2015)) and FX Measures (which consists of Daily Rate, Monthly Average Rate, Half-yearly Average rate and Annual Average Rate). Entering the data for daily rate is very straight forward, where I would simply upload the daily rate into the right date in Palo, but for Monthly Average Rate, instead of loading the actual average rate into Palo, we would like Palo to do this. I have done some research and Google-ing and I think the best way to do this is using the rule, where:

    [Monthly Average Rate] = [Total Daily Rate for the month] / [Number of days in that month]

    which in Palo the formula looks like this:

    ['Monthly Average Rate'] = C: PALO.DATA("PASCO", "RUBBISH CUBE", !'Day', !'FX Measures') / PALO.ECHILDCOUNT("PASCO", "Day", "1/07/2009")

    The formula does not work, unfortunately. I think the issue here is with the element in the Palo.Echildcount formula. I think this should be the consolidated element so it can count the number of children under it, but when I click on the drilldown, I do not have the option for the consolidated element (eg July 2009, August 2009, etc).

    Could you please let me know if I have done something wrong here? Or do I have to use another formula? I came across another suggestions in the Jedox forum where I may have to do the count and store it in another element, maybe call this NumberOfDays?

    Any assistance or suggestion will be much appreciated.

    Thank you!



    And below is response from Holger:



    I would prefer so save the number of days per month and refer that cell for avg-calculation. Of course ECHILDCOUNT should work. Therefore the dimension should look like this:

    2011-07

    -2011-07-01

    -2011-07-02

    -2011-07-03

    ...

    2011-08

    -2011-07-01

    -2011-08-02

    ...

    For the number of days in July 2011 the ECHILDCOUNT would okk like this

    PALO.ECHILDCOUNT("PASCO", "Day", "2011-07")

    If you don't need day related stuff besides calculation of exrate avg, I would defintely prefer to just saving the number of days in a - lets say "Settings-Cube" that you can refer with PALO.DATA.

    Hope you guys find it helpful :)