Amarnath had the quiz answer about 99% there.
Note, I moved hie paragrapgs around a little to make it more meaningful but his reply was:
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.
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
The problem with his answer is he needs to do a fix before his existing fix to clear out the values as he is writing to an upper level intersection and the code would not be rerunnable (is that a word?). It is nice that he tried to use the last quiz's answer as part of his response, but I was not bright enough to think about having that happen. It shows he actually read my solution :)
Clementine's modified version of his code is:
FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))
"Sales" (
"Major Market Sales" = "Sales"/@ISUDA(Market,"Major Market");
"Small Market Sales= "Sales"/@ISUDA(Market,"Small Market");
)
ENDFIX
AGG(PRODUCTS,MARKET);
In this case, Clementine would have to have the agg statement but gets the ability to drill into Market to see what Markets made up each of the calculated members.
If Clementine was not so picky about Fix statements she could have done a fix around the Agg to only do the Aggs in the Actualscenario, but that would have broken her rule.(And added efficiency.). If the requirement was not to use the UDA, you could have also used the attribute dimension and would not have needed to calculate at all or you could replace the @ISUDA with @ISATTRIBUTE. There are a number of other solutions available and Jared had the beginnings of one, but his solution had a few problems.But is was a really good try. A little modification and it would most likely work. If you have another solution, please post it, In Calc scripts, like many other things in Essbase, there is more than one way to accomplish things.
Only two people tried to answer the quiz. Do I need to make the easier or harder, keep them as they are, forget about them or just post them as tips instead of quizes so you don't have to think too hard?
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 ...
1 year ago
Posts
4 comments:
Glenn,
I like the quizzes. Keep 'em coming!
Jared
As per your Question, The SUM of Major Market sales should be present at "Major Market Sales".
But as per your solution,
"Sales" (
"Major Market Sales" = "Sales"/@ISUDA(Market,"Major Market");
"Small Market Sales= "Sales"/@ISUDA(Market,"Small Market");
)
You are just assigning the last sales of "Major Market" and last sales of "Small Market" to "Major Market Sales" & "Small Market Sales" and not adding to get the total sales of "Major Market" to "Major Market Sales" and total sales of "Small Market" to "Small Market Sales"
Was my interpretation of your Question wrong?
your interpetation was not wrong, I perhaps should have worded it differently. If you look at my solution, it is agging the dimension to get the sum, but doing it at level zero allows the users to see the totals at intermediate levels as well.
Thanks Glen for the reply.
I was at a restaurant last night for dinner somehow and was thinking about your solution and I realized that what you have said is right.
My solution doesn't give the intermediate level results which is lacking in my solution. I guess I need to think in different angles.
I really like the quizzes.
Post a Comment