In a cavern, in a canyon,
Excavating for a mine,
Dwelt a miner, forty-niner,
And his daughter Clementine.
Now Clementine in addition to being beautiful was a bit particular. She
was an Essbase Calc script wizard. As I said she was particular, this proves it.
She hated the word IF because she know if statements are slow and Clementine was a fast woman. She figured if she was going to Fix something it should be fixed the first time and not again.
She was asked; well it was highly requested or she would be swimming with the fish (and from the chorus, we all know how that turned out)
Ruby lips above the water,
Blowing bubbles soft and fine,
to create a calc script in the Sample.Basic database (BSO) that would take Sales and and add them into a one of two members.
Major Market Sales
Small Market Sales
(Members she had to create in the Accounts Dimension)
The calculation should be done on level zero of all dimensions and be based on the Market UDAs. Only one fix statement and no if statements.
Can you help Clementine?
I'm so lonely, lost without her,
Wish I'd had a fishing line,
Which I might have cast about her,
Might have saved my Clem
Note, Clementine does not refer to any real person, the song just popped into my head, so I thought it would be a fun to include it in the quiz and perhaps throw you off a bit.
OK, now for some other news, Edward Roske created yet another linked in group, but this one is just for us Essbase types. Not Hyperion, Not Oracle, Not planning, but just Essbase. If you want to be like one of the cool kids, come link it at http://www.linkedin.com/groups?gid=2905269
ODI in the hybrid database world – Amazon Redshift – AWS CLI
-
Written on June 5, 2023 by Rodrigo Radtke de Souza Hi all, probably this is
the last post of this series on how to load data from on-premises databases
to ...
10 months ago
Posts
10 comments:
If I have understood it right, I need to add all the sales which belong to Major Market to "Major Market Sales" and all sales which belong to Small Market to "Small Market Sales". So, here goes my solution and I am using your solution of Quiz 2 for this.
I will clear my "Major Market Sales" and "Small Market Sales" before calculating these accounts.
FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))
"Sales" (
"Major Market Sales"->"Market"->"Product" = "Major Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Major Market");
"Small Market Sales"->"Market"->"Product" = "Small Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Small Market");
)
ENDFIX
Since boolean function results in 0 or 1, if the current member has UDA as "Major Market" then the boolean function will result in 1, if not it will result in 0 and dividing by 0 results in #Missing. By this method it is not required to use an IF statement.
I hope my understanding and my solution is right as per your Question.
I'll give it a go:
FIX (@LEVMBRS(Year,0), @LEVMBRS(Product,0), @LEVMBRS(Market,0), Actual, Budget)
"Small Market Sales" = @SUMRANGE(Sales, @UDA(Market,"Small Market") AND @LEVMBRS(Market,0));
"Major Market Sales" = @SUMRANGE(Sales, @UDA(Market,"Major Market") AND @LEVMBRS(Market,0));
ENDFIX
CALC DIM (Product);
This worked for me. However, depending on exactly what markets you want to see the new accounts at, it might need tweaking. For example, does it make sense for these values to only exist at level 0 markets? If not, I suppose you could include ALL members of Market in the FIX. But you said the calc "should be done on level zero of all dimensions", so I didn't. Either way, you would need to leave out the two new accounts on future calculations of the Market dimension to avoid double counting.
And by the way, I hate using "AND" in FIX statements; I've found it produces inconsistent results. But if I didn't, I got double counts because some level 1 members were tagged with the UDAs.
I was wondering whether you have received my comments or not?
I guess you might be really busy with your work.
Amarnath,
I did get your answer but did not want to publish it before others had a chance to try to figure it out. The one problem with your answer is your statement.
"I will clear my "Major Market Sales" and "Small Market Sales" before calculating these accounts."
That would require a second fix statement.
I'm glad to see you used my previous quiz in your answer, but it was not necessary. Doing this would prevent you from seeing the individual markets that make up the value. (might or moght not be a requirement in the real world).
Jared,
While my thinking was more along the lines of what Amarnath though (I will publish the complete solution soon), your answer has the beginnings of another way to do it. But a couple of things. I think it might be slower (I did not test it). and If you are fixing on the level zero markets do you really need the and statement? finally, I did not test, so I don't know, but without testing, would you not get the total for small market in each market member using the sumrange function?
Glenn,
Oops. You're right. I forgot I'd already FIXed on the the level 0 members; the AND is not necessary.
Jared
You are right Glenn. A second FIX statement is require to clear the "Major Market Sales" and "Small Market Sales".
[Glenn]
to see you used my previous quiz in your answer, but it was not necessary. Doing this would prevent you from seeing the individual markets that make up the value. (might or moght not be a requirement in the real world).
[Amarnath]
I am not sure on this but I was considering that the Market Dimension is not a balanced Hierarchy. In such case, @RELATIVE("Market",0) will give me the leaf member irrespective at what level of Hierarchy it is.
May be I could have used @LEVMBRS("Market",0) instead of @RELATIVE("Market",0)
Amarnath,
Either level zero or @relative would work.
and Glenn's "soon to be published solution" is ?....
Look later in my blog, there is a whole post on the answer
Post a Comment