• Replies 3 replies
  • Subscribers 962 subscribers
  • Views 413 views
  • Users 0 members are here
Related

CASE WHEN function for adding value into column

Odele Peter
$organization

Total newbie analytics question ... 

Modifying my first seats and tickets pivot table - I'm trying to add a column that just has a value based on the perf id as an experiment (to be used in a formula once i get this working). 

I'm using 

case
WHEN [Performance ID]=17917 THEN 357
ELSE 0
END

I want the value in the column to be 357 for the row with the perf id = 17917

But I'm getting an error that says Error in "CASE" statement in 'condition_1': Expecting parameter of type 'Boolean' that consists of expression types 'Member Expression'. Instead found "Dimension Expression'. 

What am I doing wrong? 

Odele

Parents
  • $organization

    I do not claim to be an expert on this at all but I did use some formulas very much like this to track our COVID returns, and I think what you need is to change the "Type" on the [Performance ID] element to "MAX" instead of "All Items" (and then make sure you have a row for Performance ID or performance code or something like that which will break out your individual performances). Here's the formula I used to show the Ticket Paid amount pre-refunds for a list of Production Seasons:

    CASE
    WHEN [Max Production Season ID]=1237 THEN 54301.25
    WHEN [Max Production Season ID]=1357 THEN 86000
    WHEN [Max Production Season ID]=1354 THEN 23512
    WHEN [Max Production Season ID]=981 THEN 161808
    WHEN [Max Production Season ID]=1218 THEN 112880
    WHEN [Max Production Season ID]=1240 THEN 49366
    WHEN [Max Production Season ID]=1014 THEN 1774.50
    WHEN [Max Production Season ID]=1000 THEN 1653.40
    WHEN [Max Production Season ID]=1229 THEN 40658
    WHEN [Max Production Season ID]=1093 THEN 8896.50
    WHEN [Max Production Season ID]=1326 THEN 14730
    WHEN [Max Production Season ID]=1244 THEN 105392.75
    ELSE 0
    END

    Hope that helps!
    Sarah Covie (she/her)
    PortTIX

Reply
  • $organization

    I do not claim to be an expert on this at all but I did use some formulas very much like this to track our COVID returns, and I think what you need is to change the "Type" on the [Performance ID] element to "MAX" instead of "All Items" (and then make sure you have a row for Performance ID or performance code or something like that which will break out your individual performances). Here's the formula I used to show the Ticket Paid amount pre-refunds for a list of Production Seasons:

    CASE
    WHEN [Max Production Season ID]=1237 THEN 54301.25
    WHEN [Max Production Season ID]=1357 THEN 86000
    WHEN [Max Production Season ID]=1354 THEN 23512
    WHEN [Max Production Season ID]=981 THEN 161808
    WHEN [Max Production Season ID]=1218 THEN 112880
    WHEN [Max Production Season ID]=1240 THEN 49366
    WHEN [Max Production Season ID]=1014 THEN 1774.50
    WHEN [Max Production Season ID]=1000 THEN 1653.40
    WHEN [Max Production Season ID]=1229 THEN 40658
    WHEN [Max Production Season ID]=1093 THEN 8896.50
    WHEN [Max Production Season ID]=1326 THEN 14730
    WHEN [Max Production Season ID]=1244 THEN 105392.75
    ELSE 0
    END

    Hope that helps!
    Sarah Covie (she/her)
    PortTIX

Children
  • $organization in reply to Sarah Covie

    Hi Sarah,

    I think you're onto something. I tried my formula with Max Production Season ID and that works as you suggested. However, I need to be more specific to the performance level for my case. But when I use Performance ID, there is no "max" option - only "# All items" and "[#] Count Unique". Must be something related to the data type and expression that I'm trying to use. I'll keep digging. 

    Odele

  • $organization in reply to Odele Peter

    Hi Odele,

    I'm looking into why that option is sometimes not available on numeric fields. Even when there isn't a "More... Max "option available though, you can always type "MAX()" into the formula editor and put the "More... All Items" version of the field in the function: MAX ( [Performance ID] ).

    However, I've entered DEV-10364 on the Defect List, and escalated it to Sisense who have confirmed it as a defect. I'm not clear yet on a work around, for how we could make Performance ID behave like Production Season ID, but I'll be looking into it.

    As for the CASE formula and a workaround in that context for MAX([Performance ID]), you might try

    WHEN ( [# unique Performance ID] , [Performance ID = 17917] )  = 1 THEN 357

    In a widget with [Performance Date and Code] and without [Performance ID] on Rows or Categories, that formula will return "1" unique Performance ID for the row containing data for Performance ID 17917, and 0 or NULL for any other rows/categories. In this formula, there is a value filter on Performance ID set to the ID we're looking for when we want to return 357 as the result. In my screenshot below I'm arbitrarily returning 39298 instead of 357.

    Best,
    Chris