topic Re: division with two if statement in QlikView

division with two if statement

Thu, 04 Aug 2016 15:37:13 GMT

Hi everyone,

The calculation in my pivot table is actually off.

I am trying to divide this expression, numerator: =IF(TODAY()>[Opp Exp Decision Date], ([Local Member Firm Fees]*[Opp Probability]/100)/295*0.5,[Local Member Firm Fees]*[Opp Probability]/100/295)

with denominator: =IF(((([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)=0),1,([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)

the two expressions alone generate the correct values, but not when I link them in division with this expression: =IF(TODAY()>[Opp Exp Decision Date], ([Local Member Firm Fees]*[Opp Probability]/100)/295*0.5,[Local Member Firm Fees]*[Opp Probability]/100/295)/IF(((([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)=0),1,([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)

the number is a little off. Can somebody please guide me on how to link the two with division?

Re: division with two if statement

Anonymous — Thu, 04 Aug 2016 16:18:17 GMT

Hi Can you share a sample app to highlight your issue?

Re: division with two if statement

sunny_talwar — Thu, 04 Aug 2016 16:28:12 GMT

Did you try using my Sum(Aggr(...)) logic? I feel that should work

Re: division with two if statement

Thu, 04 Aug 2016 16:37:19 GMT

can you type out the whole expression for me? I tried using the sum aggr expression and I see the expression is in error.

=sum(aggr(IF(TODAY()>[Opp Exp Decision Date], ([Local Member Firm Fees]*[Opp Probability]/100)/295*0.5,[Local Member Firm Fees]*[Opp Probability]/100/295))),IF(((([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)=0),1,([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)))

Re: division with two if statement

sunny_talwar — Thu, 04 Aug 2016 17:22:04 GMT

Can you provide me the list of dimensions used in your pivot table?

Re: division with two if statement

Thu, 04 Aug 2016 17:25:27 GMT

I would like to provide a sample excel file, where do I attach it?

Re: division with two if statement

sunny_talwar — Thu, 04 Aug 2016 17:40:40 GMT

I just want to know the names of the dimensions.

Re: division with two if statement

Thu, 04 Aug 2016 17:59:51 GMT

dimension used for the numerator:

these are the used dimensions. Let me know if this is what you need.

Re: division with two if statement

Thu, 04 Aug 2016 18:06:53 GMT

I am trying to show Column N in Qlikview. Somebody please help!

Re: division with two if statement

Thu, 04 Aug 2016 18:08:06 GMT

Hi Brian,

I added a sample file in the bottom just now.

Re: division with two if statement

sunny_talwar — Thu, 04 Aug 2016 18:14:06 GMT

Yes, but instead of a snapshot, can you type there names here

Re: division with two if statement

Thu, 04 Aug 2016 18:17:11 GMT

Subcommunity

opp partner

entity name

opp description

opp probability

overall Opp value