topic Re: Need Calculation Help - Multi Calculation in QlikView

Need Calculation Help - Multi Calculation

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

Hello:

I need some assistance calculating the following scenario:

Fields involved are APP ALLOCATION which can be 1 or a lesser percentage like 0.02. 0.05, 0.1, etc, Charge Amount in dollars with two decimal places, and then PRODUCT AVAILABILITY which is a text field
The two text values under PRODUCT AVAILABILITY I need to do special calculations on are "Server Port Connectivity 1GB (GBL)" and "Server Port Connectivity 10GB (GBL)" (without the quotes for both of course)
The current expression is =[Charge Amount]*[APP ALLOCATION] - This works fine for those items where there's more than one application on a host and the applications split the cost - However the two Server Port Connectivity charges get charged to each application at the full amount

Here's an example of where the above expression works fine: APP ALLOCATION = 0.25, Charge Amount = 900, PRODUCT AVAILABILITY = Workgroup Server Business, so using the expression =[Charge Amount]*[APP ALLOCATION] I get $225 which is correct.

However, when PRODUCT AVAILABILITY = Server Port Connectivity 1GB (GBL) or Server Port Connectivity 10GB (GBL), the APP ALLOCATION number should not be used and the full Charge Amount should be used. For example, when PRODUCT AVAILABILITY like "Server Port Connectivity" use full Charge Amount (PRODUCT AVAILABILITY = Server Port Connectivity 1GB (GBL), Charge Amount = $115, then my total for that line item should be $115).

Any ideas on how I incorporate this scenario into the expression? Does this make sense? Thanks in advance.

Re: Need Calculation Help - Multi Calculation

sangeess21 — Mon, 30 Jan 2017 19:01:55 GMT

Try this:

if(not Match([PRODUCT AVAILABILITY],’Server Port Connectivity 1GB(GBL)’,’Server Port Connectivity 10GB(GBL)’),[Charge Amount]*[APP ALLOCATION], [Charge Amount])

Thanks,

Sangeetha

Re: Need Calculation Help - Multi Calculation

vinieme12 — Mon, 30 Jan 2017 19:03:49 GMT

Can you post a sample app with dummy data that best replicates your scenario

Re: Need Calculation Help - Multi Calculation

pnn44794 — Mon, 30 Jan 2017 21:26:38 GMT

Hello Sangeetha:

Your expression works with a couple of little tweaks. For whatever reason, the single quote marks came across non-normal when I copied the expression, but I simply changed them to my keyboard's style. Then and this is my fault, there's a space between GB and (GBL). I made those two changes and it worked like a champ!!! Thank you very much for your assistance.

Quick question, if I come across another item where I have to do the same thing by adding it to this expression, I simply put a comma after 10GB (GBL)', right? In other words like this:

if(not Match([PRODUCT AVAILABILITY],’Server Port Connectivity 1GB (GBL)’,’Server Port Connectivity 10GB(GBL)’, 'New Item To Not Multiply'),[Charge Amount]*[APP ALLOCATION], [Charge Amount])

Re: Need Calculation Help - Multi Calculation

pnn44794 — Mon, 30 Jan 2017 21:34:39 GMT

Hello Vineeth:

Thanks for your reply. I believe Sangeetha has provided a solution, but I definitely appreciate your interest and reply. Thanks again.

Re: Need Calculation Help - Multi Calculation

sangeess21 — Tue, 31 Jan 2017 15:00:52 GMT

Yes. Just add it to the expression and it would work as expected. Please close the thread if you have your question answered.

Thanks,

Sangeetha

Re: Need Calculation Help - Multi Calculation

pnn44794 — Tue, 31 Jan 2017 15:26:31 GMT

Thank you. I'll mark your previous response as correct. Again, thank you for your help.