topic Re: Expression Calculation Help in QlikView

Expression Calculation Help

pnn44794 — Fri, 11 Oct 2019 22:10:19 GMT

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.

Re: Expression Calculation Help

pnn44794 — Fri, 11 Oct 2019 22:11:05 GMT

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:

Re: Expression Calculation Help

sunny_talwar — Sun, 24 Dec 2017 00:17:47 GMT

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?

Re: Expression Calculation Help

pnn44794 — Sun, 24 Dec 2017 00:33:16 GMT

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?

Re: Expression Calculation Help

sunny_talwar — Sun, 24 Dec 2017 00:34:50 GMT

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?

Re: Expression Calculation Help

pnn44794 — Sun, 24 Dec 2017 00:45:43 GMT

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.

Re: Expression Calculation Help

sunny_talwar — Sun, 24 Dec 2017 00:48:37 GMT

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.....

Re: Expression Calculation Help

pnn44794 — Fri, 11 Oct 2019 22:12:18 GMT

Ok. I’m missing something simple.

Re: Expression Calculation Help

sunny_talwar — Sun, 24 Dec 2017 03:28:48 GMT

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'

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?

Re: Expression Calculation Help

pnn44794 — Fri, 11 Oct 2019 22:13:20 GMT

Hello Sunny:

Re: Expression Calculation Help

pnn44794 — Mon, 25 Dec 2017 19:02:22 GMT

Did my previous reply make any sense to you? If not, I can try to explain further.

P.S. Happy Holidays!

Re: Expression Calculation Help

sunny_talwar — Tue, 26 Dec 2017 02:53:41 GMT

So, if we go each chart at a time, do you know what are the numbers you expect to see in the first chart for example? I mean I am sort of not very clear as to what you are trying to convey here

If you look at the first tab, VSI and VSI-D Forecast Readiness, I'm trying to calculate the number of CPU's and Memory amount for each row listed.

What you be able to convert the above def. into numerical terms. I know you have been providing a lot of details, but not sure why, but still having a lot of difficulty to understand what is needed here.

Best,

Sunny

Re: Expression Calculation Help

pnn44794 — Wed, 27 Dec 2017 23:47:16 GMT

Hello Sunny:

My apologies for the delay in responding. I believe your initial assessment of the calculated dimensions conflicting with the expressions is what my problem is. So, I’m in the process of changing the data model. I’m not sure if it’s going to work or not, but we’ll see. As always, thank you for your help and I’ll let you know how my changes go.

Re: Expression Calculation Help

pnn44794 — Thu, 28 Dec 2017 02:17:38 GMT

Hello Sunny (and Anyone Else Following this discussion):

So, I've solved the problem, but it took you (Sunny) to point it out. In this case, I cannot have an item with three rows where the item_sku is the same. It just won't work. So, I eliminated the duplicate item_sku numbers, tweaked my calculated dimensions and expressions and viola! I'm now getting what I was after which was a top 20 listing by quantity to include the associated CPU and Memory quantities.

Many thanks to Sunny for pointing out the conflicts I had between the calculated dimensions and expressions.

Sunny, I will mark your last response in that regard as the correct response, As always my friend, thank you for your assistance. It's always appreciated. Happy Holidays!