tag:blogger.com,1999:blog-2615703102299902978.post7546466426046511672..comments2024-02-03T00:03:10.178-08:00Comments on Glenn Schwartzberg's Essbase Blog: Quiz 3GlennShttp://www.blogger.com/profile/08532634180859769798noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-2615703102299902978.post-61293268666477515762010-10-13T10:44:00.105-07:002010-10-13T10:44:00.105-07:00Look later in my blog, there is a whole post on th...Look later in my blog, there is a whole post on the answerGlennShttps://www.blogger.com/profile/08532634180859769798noreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-89435674592442728522010-10-13T08:12:06.284-07:002010-10-13T08:12:06.284-07:00and Glenn's "soon to be published solutio...and Glenn's "soon to be published solution" is ?....Georgenoreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-57981287938325308722010-04-09T08:21:25.546-07:002010-04-09T08:21:25.546-07:00Amarnath,
Either level zero or @relative would wor...Amarnath,<br />Either level zero or @relative would work.GlennShttps://www.blogger.com/profile/08532634180859769798noreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-80250173631358279212010-04-08T06:30:31.895-07:002010-04-08T06:30:31.895-07:00May be I could have used @LEVMBRS("Market&quo...May be I could have used @LEVMBRS("Market",0) instead of @RELATIVE("Market",0)amarnathhttps://www.blogger.com/profile/12749240552285136096noreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-80890939972216563252010-04-08T06:29:16.244-07:002010-04-08T06:29:16.244-07:00You are right Glenn. A second FIX statement is req...You are right Glenn. A second FIX statement is require to clear the "Major Market Sales" and "Small Market Sales".<br /><br />[Glenn]<br />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). <br />[Amarnath]<br />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.amarnathhttps://www.blogger.com/profile/12749240552285136096noreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-7312566951856773582010-04-07T05:48:33.911-07:002010-04-07T05:48:33.911-07:00Glenn,
Oops. You're right. I forgot I'd...Glenn,<br /><br />Oops. You're right. I forgot I'd already FIXed on the the level 0 members; the AND is not necessary.<br /><br />JaredAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-34988171134685228422010-04-05T12:57:33.145-07:002010-04-05T12:57:33.145-07:00Amarnath,
I did get your answer but did not want ...Amarnath, <br />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. <br /><br />"I will clear my "Major Market Sales" and "Small Market Sales" before calculating these accounts."<br /><br />That would require a second fix statement. <br />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). <br /><br />Jared, <br />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?GlennShttps://www.blogger.com/profile/08532634180859769798noreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-44095212252003869662010-04-05T08:30:44.798-07:002010-04-05T08:30:44.798-07:00I was wondering whether you have received my comme...I was wondering whether you have received my comments or not?<br /><br />I guess you might be really busy with your work.amarnathhttps://www.blogger.com/profile/12749240552285136096noreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-22640124185559067842010-03-31T12:53:12.824-07:002010-03-31T12:53:12.824-07:00I'll give it a go:
FIX (@LEVMBRS(Year,0), @LE...I'll give it a go:<br /><br />FIX (@LEVMBRS(Year,0), @LEVMBRS(Product,0), @LEVMBRS(Market,0), Actual, Budget)<br /> "Small Market Sales" = @SUMRANGE(Sales, @UDA(Market,"Small Market") AND @LEVMBRS(Market,0));<br /> "Major Market Sales" = @SUMRANGE(Sales, @UDA(Market,"Major Market") AND @LEVMBRS(Market,0));<br />ENDFIX<br /><br />CALC DIM (Product);<br /><br />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.<br /><br />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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-89326832568500588502010-03-31T10:01:12.154-07:002010-03-31T10:01:12.154-07:00If I have understood it right, I need to add all t...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.<br /><br />I will clear my "Major Market Sales" and "Small Market Sales" before calculating these accounts.<br />FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))<br /><br />"Sales" (<br /> "Major Market Sales"->"Market"->"Product" = "Major Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Major Market");<br /> "Small Market Sales"->"Market"->"Product" = "Small Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Small Market");<br /><br />)<br />ENDFIX<br />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.<br /><br />I hope my understanding and my solution is right as per your Question.amarnathhttps://www.blogger.com/profile/12749240552285136096noreply@blogger.com