Expression Calculation Help

[pnn44794]

Hello:

I'm trying to calculate CPU and Memory amounts, and then sum the totals at the bottom of a straight table.

As always, any and all help is appreciated.  Thanks in advance.

[pnn44794]

Here's some additional information that may assist anyone trying to help.

I have the following calculated dimensions that I label PRODUCT and PRODUCT CLASS, respectively:

[sunny_talwar]

These expressions are contradicting your dimensions

Expressions

For CPU =If(Foundation_2 = 'VSI CPU' and (Foundation_3 = 'VSI Bronze CPU' or Foundation_3 = 'VSI Silver CPU'), effective_item_qty * cpu_cores)

For Memory =If(Foundation_2 = 'VSI MEM' and (Foundation_3 = 'VSI Bronze MEM' or Foundation_3= 'VSI Silver MEM'), effective_item_qty * cpu_cores)

Dimensions

=If(Foundation_2 = 'VSI Base', 'VSI Base')

=If(Foundation_3 = 'VSI Bronze', 'VSI Bronze', If(Foundation_3 = 'VSI Silver', 'VSI Silver'))

How do you think this will work if your dimensions is excluding those values you have in your expression?

[pnn44794]

Hello Sunny:

Thanks for the reply.  I'm not sure that I follow you.  One is supposed to be for when Foundation_2 = VSI CPU and the other is supposed to be for when Foundation_2 = VSI MEM.  Each then further broke down by Foundation_3 being Bronze or Silver MEM or CPU.  Does that make sense?  I have to exclude, for example, Foundation_3 = 'VSI Gold CPU' (that's another table specifically for VSI Gold CPU and no, I do not have that working yet.).

So, to answer your question, that's what I don't know (not a good answer on my part).  For the dimensions, I'm trying to limit the table to just items that have a Foundation_2 = 'VSI Base' and then just Foundation_3 = either 'VSI Bronze' or 'VSI Silver' and then calculate the CPUs and Memory.  When I don't do the calculated dimensions and you choose the drop down arrow within the straight table for those columns, all available options within the data model show up, even though they are all not really available.  So, in short, with the calculated dimensions, I'm trying to just show what is available for the table.  Make sense?

[sunny_talwar]

Your dimension is saying this

=If(Foundation_2 = 'VSI Base', 'VSI Base')

and your expression is saying this

If(Foundation_2 = 'VSI CPU'

Do you see the contradiction my friend?

[pnn44794]

Ah ha! I believe I do. Let me make a change and see if it works. The expression needs to match the calculated dimension I believe or vice-versa.

[sunny_talwar]

Exactly because dimension is picking VSI Base, where as expression wants to pick VSI CPU. and in this case Dimension wins (because you might have selected 'Suppress When Value Is Null') so anything which is not VSI Base is just suppressed.....

[pnn44794]

Ok.  I’m missing something simple.

[sunny_talwar · Accepted solution]

The second dimension is still the issue

Dimension

=If(Foundation_3 = 'VSI Bronze', 'VSI Bronze', If(Foundation_3 = 'VSI Silver', 'VSI Silver'))

Expression

Foundation_3 = 'VSI Bronze MEM' or Foundation_3= 'VSI Silver MEM'

or

Foundation_3 = 'VSI Bronze CPU' or Foundation_3 = 'VSI Silver CPU'

The problem is that I am not completely sure as to what is needed. Are you able to share a qvw to show what you are getting and what is that you want?

[pnn44794]

Hello Sunny: