many "ifs"

[jacek27031]

what's wrong with my code? 

if((([BP2]='XXX'), ([Cprice] - sum(([XXX CDF] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))))),

if(((Front='1'), ([Cprice] - sum(([SRP] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))))), ([Cprice] - sum(([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))))

1st condition

if((([BP2]='Terg'), ([Cprice] - sum(([TERG CDF] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))))))

2nd

if(((Front='1'), ([Cprice] - sum(([SRP] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))))

Else (neither 1 & 2)

([Cprice] - sum(([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))))

[stevedark]

Hi @jacek27031 

The thing that jumps out at me is that you have some parts which are wrapped in a sum statement, and some parts that are not.

So, if there are more than one distinct values for either BP2 or Cprice then the expression will not know what do do.

This may make sense if you have BP2 as a dimension on the chart, as then there will only ever be one value for each data point.

Cprice on the other hand, I suspect should either have an aggregation around it, or be contained within the sum statement, for example:

(sum([Cprice] - ([TERG CDF] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))))

or

sum(Cprice) - (sum(([TERG CDF] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))))

Note that those two expressions are doing two very different things and will give very different results.

The best way to go is always to build up to larger statements in small increments, try each bit out independently before adding it to the whole.

Similar to why you need the Cprice field in an aggregation, the sum statement you have will only work if TERG CDF, ER Plan, T1_GP and T2_GP all exist in a single row in the data model. If they are in different tables then you will need separate aggregations for each.

Hope that makes sense?

Steve

[jacek27031]

Thank you so much! 

Let me say this in another way.

[BP2] and "XXX" is the one of the seller's name in dimension table. The rest are facts. 

The below code works fine

if(([BP2]='XXX'),
(([Cprice] - ([XXX CDF] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))),
(([Cprice] - ([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))))

and calculates a unit discount for seller XXX and other sellers (if-else). What I need to do now is add another condition and the next condition must be met for all sellers, so also for partner XXX. This condition is: 

if(([Front]='1'),
(([Cprice] - ([SRP] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))),
(([Cprice] - ([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))))

Both "if" conditions for ",else_expr" should be the same:

(([Cprice] - ([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))))

Now I need to combine both conditions into one code 🙂 Thank you again for your time and any tips!

J. 

[jacek27031]

In other words - I need to create 2 measures:

Unit discount:

[Cprice] - (([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))

Total discount:

sum([# STI Plan]) * ([Cprice] - (([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))

Both measures have the same 2 conditions. 

[stevedark]

Hi @jacek27031 

I'm sorry, but I don't entirely follow what you are trying to do, or where it is failing.

Is it that you need to nest your IF statements, as follows:

if(([Front]='1'),
  (([Cprice] - ([SRP] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))),
  if(([BP2]='XXX'),
    (([Cprice] - ([XXX CDF] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))),
    (([Cprice] - ([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))))
  )
)

You can nest IF statements as much as you like, just try and keep track of where your brackets are pairing to. Indentation can help with this. Putting some code into variables can also help.

Hope that points you in the right direction.

Steve

[jacek27031]

Thank you so much. I'll go check it out and let u know. 

[stevedark]

A simpler approach may be to create a new field in the load, with the rate that applies on that row:

LOAD
   BP2,
   [XXX CDF],
   [CDF Calc],
   if([BP2]='XXX', [XXX CDF], [CDF Calc]) as [Client CDF],
   etc...

This way Client CDF will always give the value you need.

It does require that all of those fields exist on the same table in the load though.

Steve