Hi!
I have calculated measure with following structure:
aggregate([X] within set set([Product category 1];[Product category 2]) )
+ aggregate([Y] within set set([Product category 3]) )*1,25
and I also have a prompt on top of the page that has the three product categories as a choice. The parameter of the prompt has been put as a slicer for the query page.
When I run the report and choose one of the options and reprompt, other things on my report page and other measures in the same crosstab work fine, but this calculated measurement does not "get sliced". Is it because I have explicitly called the sets so slicers cant affect it? Any workaround?
Still having troubles with this. Is it possible to build some kind of workaround with "if" functions? I tried something like:
if ([Product category 1] in ?pCategories?) then (aggregate([X] within set set([Product category 1]) ) ) else (0) but it gave me error for not being able to compare sets... also tried to substitute [Product category 1] with its MUN, but same error appeared. Any ideas?
Maybe I have missed something in your explanation, but I see no references to slicers in any of your functions. As you said, you have explicitly called out the sets.
I think you might be looking for the functionality of the filter() function.
so you want to slice this whole calculation by what ever is selected in your product prompt?
aggregate([X] within set set([Product category 1];[Product category 2]) )
+ aggregate([Y] within set set([Product category 3]) )*1,25
so if in the product prompt you chose "Product category 1" how would you like to see your calculation be?
Oh sorry I might have forgotten to actually show the prompt & slicer.
So I have a prompt for product category with possible values of Product category 1, Product category 2 and Product category 3. then I have made a data-item for the slicer as follows:
set(#promptmany('pProductCategory';'MUN';'defaultMember([Product category hierarchy])')#)
I have also tried making a detail filter with
[Product category hierarchy] in (?pProductCategory?)
but it doesn't seem to have any effect either.
If I chose Product category 1 from the prompt, I would like the calculation to be just "aggregate([X] within set set([Product category 1]) )". the measure X I have is cubic meters, but as it is not measured for product 3 we have to make an estimation with coefficient 1.25 :) I hope this sheds light to my problem and you might be able to guide me to the right direction.
Thanks for the effort so far!
I have tried all kinds of things, amongst those I tried a new way of calculating my calculated member:
tuple([Product category 1];[X])+tuple([Product category 2];[X])+tuple([Product category 3];[Y])*1,25
Even then the tuple seems to also ignore my slicer for product category (it performs all the tuples even I have only selected category 1 from my prompt). Is it supposed to work this way?
EDIT: Can I somehow insert the prompt parameter in the tuple function even though [Product category 1] for example is from the same hierarchy as the prompt values? What I mean is, when [Product category 1] is selected from the prompt, the first part of the tuple hassle above would be "read" by the code as:
tuple([Product category 1];[Product category 1];[X])
but when only product category 2 is chosen:
tuple([Product category 1];[Product category 2];[X]) and therefore it would return zero (or null perhaps?).
you can try to use prompt macros and a case logic to specify the calculation base on the selected value in the prompt.
or you can try something like :
Case
When (?pProductCategory? = '[Product category 1]')
then aggregate([X] within set set([Product category 1]) )
When (?pProductCategory? = '[Product category 2]')
then ...
Else ()
End
That's true, but need to make 7 cases I believe as the prompt is multiple choise ::) Anyway that could work as a workaround in case I can't solve it any other way. Thanks a lot, did not think about that!
I would then probably use something like:
Case
When ('[Product category 1]' in (?pProductCategory?) AND '[Product category 2]' in (?pProductCategory?))
then aggregate([X] within set set([Product category 1];[Product category 2]) )
...
for the cases where multiple choices are selected? Or would it still actually select the first applicable case (therefore rendering this solution basically non-valid)?
so your prompt is multiple select?
How about something like this:
First create two itsems:
Prod_Cat_Multiplier_100 :=
intersect(set([Product category 1];[Product category 2]) ;
set(#promptmany('pProductCategory';'MUN';'defaultMember([Product category hierarchy])')#) )
Prod_Cat_Multiplier_125 :=
intersect(set([Product category 3]) ;
set(#promptmany('pProductCategory';'MUN';'defaultMember([Product category hierarchy])')#))
Idea is to get sets of whatever is selected in categories requiring different multipliers.
Then the measure could be something like
aggregate([X] within set [Prod_Cat_Multiplier_100]) * 1.00
+
aggregate([Y] within set [Prod_Cat_Multiplier_125]) * 1.25
and because 1+NULL equals NULL:
if(aggregate([X] within set [Prod_Cat_Multiplier_100]) * 1.00 is NULL)
then (0)
else (
aggregate([X] within set [Prod_Cat_Multiplier_100]) * 1.00
)
+
if(aggregate([Y] within set [Prod_Cat_Multiplier_125]) * 1.25 is NULL)
then (0)
else (
aggregate([Y] within set [Prod_Cat_Multiplier_125]) * 1.25
)
That sounds like it should work, I need to give it a try when my brain wakes up for the Monday.
Thank you everyone for your help!