Forum Discussion

Apd2024's avatar
Apd2024
Data Storage
02-02-2023
Solved

Custom Column for Pivot

Hello all, 

I am trying to create a custom column with the following conditions:

IF

( (SUM([Sale Price])<361000, [Sale Price ]/1.2,  SUM([Sale Price ])), 
  (SUM([Sale Price])<450000, [Sale Price ]+21350/1.3,  SUM([Sale Price ])),
  (SUM([Sale Price])>450001, [Sale Price ]/1.5,  SUM([Sale Price ])))

However, I am getting "syntax error: unexpected token "IF"

Any w=help would be appreciated. Thank you. 

  • david-h's avatar
    david-h
    02-03-2023

    Hi,

    Are you adding a column in the elasticube ? If so, indeed the IF does not exist alone, and need to be typed. So you will need in your case to use one of this function from this page : Logical Functions (sisense.com)

    You can also use CAS / WHEN, like in this page Adding a Custom Column (sisense.com) :

    CASE
    WHEN [PriceVsCompetitor%] &lt; -0.05 THEN '-5%'
    WHEN [PriceVsCompetitor%] &gt;= -0.05 AND [PriceVsAvgCompetitor%] &lt;= 0.05 THEN '-5%/+5%'
    WHEN [PriceVsCompetitor%] &gt; 0.05 THEN '+5%'
    end

    Let me know if this works for you.

    Best, David.

1 Reply

Replies have been turned off for this discussion
  • david-h's avatar
    david-h
    Cloud Apps
    02-03-2023

    Hi,

    Are you adding a column in the elasticube ? If so, indeed the IF does not exist alone, and need to be typed. So you will need in your case to use one of this function from this page : Logical Functions (sisense.com)

    You can also use CAS / WHEN, like in this page Adding a Custom Column (sisense.com) :

    CASE
    WHEN [PriceVsCompetitor%] &lt; -0.05 THEN '-5%'
    WHEN [PriceVsCompetitor%] &gt;= -0.05 AND [PriceVsAvgCompetitor%] &lt;= 0.05 THEN '-5%/+5%'
    WHEN [PriceVsCompetitor%] &gt; 0.05 THEN '+5%'
    end

    Let me know if this works for you.

    Best, David.