IIF (lots of IIF)

Comments

• gordk St.Catherines On. Canada.

  January 2014 edited 11:40AM

  the first error is the wrong type of brace
  the iif in a memo should be enclosed in [ ] braces
  ie
  [IIF(expression to evaluate,true value,false value)]
  
  i suggest you try using frs builtin expression builder to help you form your expression
• technisoft

  January 2014 edited January 2014

  I have no faith in the memo's IFF() expression calculator, nor its builder.
  It may work for simple statements but for such a complex formulation I would never consider it putting there.
  
  Move the code into the script and handle it there. This will also allow you to structure the code in a readable manner and debug it efficiently.
  
  I think that using a decimal comma instead of a decimal point is another source of error.
• Petter S.

  February 2014 edited 11:40AM

  Hi.
  
  The message says: " ')' expected".
  As far as I can count you have 15 left braces, and just 14 right braces. So I would guess that one more ')' should do the trick.
  
• Mrfra62

  February 2014 edited 11:40AM

  @gordk
  did try using the expression builder, never made the formula to work
  @technisoft
  don't know how to use code or script or to handle it. even don't know where to find that tool in the report builder.
  @Petter S.
  you're right, it was one bracket left. now I dont get error message. but formula doesn't give right outcome. if you would put the excel formula in a excel sheet and use as Saldo= 900, then excel returns the right value of 135 but the fast report expression returns 40
  suppose saldo= 182.83, excel returns 40 and fast report returns 775
  excel returns the right value.....
  whats wrong with my formula ?
  why arent the values the same ?
• Petter S.

  February 2014 edited 11:40AM

  Hi.
  
  A couple of things I didn't notice at first:
  - In my FR I must use ',' between statements, not ';' (in your exemple you have one ',' in the second IIF...), and '.' as decimal point. Maybe this can be set up somewhere - i don't know...
  - if 'Saldo' is av variable, then the syntax should be '<Saldo>'
  - if 'Saldo' comes from a database/query the syntax should be <MyDb."Saldo">
  
  I've tested this one:
  
  [IIF(<Saldo>=0, 0.0, IIF(<Saldo><=266.69, 40, IIF(<Saldo><=2500, 0.15*<Saldo>, IIF(<Saldo><=5000, 375 + (0.10*(<Saldo>-2500)), IIF(<Saldo><=10000, 625 + (0.05*(<Saldo>-5000)), IIF(<Saldo><=200000, 875 + (0.01*(<Saldo>-10000)), IIF(<Saldo><=1000000, 975 + (0.005 *(<Saldo>-200000)), 6775)))))))]
  
  where <Saldo> is a variable, and I seems to get the right output (900 -> 135, 182.83 -> 40)
• Mrfra62

  February 2014 edited February 2014

  Thanks for your reply. Saldo is indeed a variable. So I put it between brackets like this: <Saldo>. This returned consequently the value: 40. The I realized that I had to calculate this formula over the sum of saldo so I put SUM(Saldo) between brackets like: <SUM(Saldo)>.
  The fomula would the be:
  [IIF(<SUM(Saldo)>=0, 0.0, IIF(<SUM(Saldo)><=266.69, 40, IIF(<SUM(Saldo)><=2500, 0.15*<SUM(Saldo)>, IIF(<SUM(Saldo)><=5000, 375 + (0.10*(<SUM(Saldo)>-2500)), IIF(<SUM(Saldo)><=10000, 625 + (0.05*(<SUM(Saldo)>-5000)), IIF(<SUM(Saldo)><=200000, 875 + (0.01*(<SUM(Saldo)>-10000)), IIF(<SUM(Saldo)><=1000000, 975 + (0.005 *(<SUM(Saldo)>-200000)), 6775)))))))]
  How ever this doesnt return the right valua.
  Then I looked at numberformats. Using "Number / 1,234.00 / %2.2n / , " the formating of the outcome is right and when i use $1,234.00 instead of 1,234.00 the outcome adds the euro sign (??????¬). But sadly the valua of the outcome isnt right.
  I dont get it at all. Use the same formula you get the desired outcome, and me .... I get the wrong outcome. Frustrating.
  Maybe it is something in the (regional) settings. However, I use the fast report in Dutch user interface and when I press F1 ... up comes help text in (US) English.
  I tied to use the variant [IIF((SUM(Saldo))=0, 0.0, IIF((SUM(Saldo))<=266.69, 40, IIF((SUM(Saldo))<=2500, 0.15*(SUM(Saldo)), IIF((SUM(Saldo))<=5000, 375 + (0.10*((SUM(Saldo))-2500)), IIF((SUM(Saldo))<=10000, 625 + (0.05*((SUM(Saldo))-5000)), IIF((SUM(Saldo))<=200000, 875 + (0.01*((SUM(Saldo))-10000)), IIF((SUM(Saldo))<=1000000, 975 + (0.005 *((SUM(Saldo))-200000)), 6775)))))))] instead of <> i use ()...this formula return consequently the value 40.
  Using the variant [IIF([SUM(Saldo)]=0, 0.0, IIF([SUM(Saldo)]<=266.69, 40, IIF([SUM(Saldo)]<=2500, 0.15*[SUM(Saldo)], IIF([SUM(Saldo)]<=5000, 375 + (0.10*([SUM(Saldo)]-2500)), IIF([SUM(Saldo)]<=10000, 625 + (0.05*([SUM(Saldo)]-5000)), IIF([SUM(Saldo)]<=200000, 875 + (0.01*([SUM(Saldo)]-10000)), IIF([SUM(Saldo)]<=1000000, 975 + (0.005 *([SUM(Saldo)]-200000)), 6775)))))))] instead of <> i use []... i get an error: Invalid variant operation
  
  I really dont get: why the value outcome is consequently 40 while I use the formula like you suggested.
  
  Could it maybe be that saldo is part of Masterdata 1 and Sum(Saldo) is in the Group footer 1 ? While the fomula is part of Group header 1
  
  Maybe you have some suggestions or tips ?
• Mrfra62

  February 2014 edited 11:40AM

  edit to my last reply:
  
  I figured it out.
  Formula works just fine.
  But after I placed it in the same part of the report, the outcome was correct.
  So I placed the formula in the group footer one where the Sum(Saldo) variable was aswell.
  Then it worked.
  
  Thanks for putting me on the right track.