Ifelse Fixed Value or Variable

Question & Answer
1

Hey everyone!

Working on something in QS and have hit a snag - in a nutshell;

I’m making a dashboard that tracks consumables usage. I want to create the following logic but cannot work out how to do it using ifelse;

If TotalCoffee < 1 then I want a fixed value of “Spread Across Both Machines” but if TotalCoffee > 1 then I want the value to be whatever my calculated field DecafMachine1 equals

In this instance, TotalCoffee is a calculated field equal to DecafMachine1 + DecafMachine2

The formula I’ve written is giving me a syntax error but I genuinely cannot work out why;

ifelse(TotalCoffee < 1, ‘Spread Across Both Machines’, ({DecafMachine1}))

Any help is greatly appreciated!
Thanks :slight_smile:

2

Hello @Steezy97 - This could be due to datatype mismatch between string and number. It seems your fixed value is text (then portion) and decafmachine1 is numeric(else portion). You need to cast the decafmachine1 into string and that should hopefully resolve your issue. Hope this helps!!

3

Hey @sagmukhe

Okay so my new formula is;

ifelse(TotalCoffee < 1, “Spread”, toString(DecafMachine1))

Now it’s giving me the following error;

Field 6433b15a-8f77-48b5-a16f-88dc396980aa in ifelse(TotalCoffee < 1, "Spread", toString(DecafMachine1)) does not exist

4

@Steezy97 Can you please share a, sample of your dataset?

5

Hey @sagmukhe

Yeah no problem, how do I share it as I cannot upload it to this comment?

Just for full clarity, DecafMachine1 and DecafMachine2 are both calculated fields made up of countifs on my dataset not fields in the dataset. Does this make a difference?

6

@Steezy97 - you can share a snapshot by putting the items in a table visual. That would help!!

7
Tenant Label Region Tag Store Tag Device Label Machine Type Local Date Local Time Day Part Global Product ID Display Name Beverage Family Cup Size Blend Type Extra Shot Espresso Milk Type Temperature Profile Flavor Type Extra Shot Flavor Beans used [g] Status MainTenant Price DaT HotD MachineMilkType
Eric’s Coffee Bar East London Wimbledon ECB-WIM-SS A800 01/05/2022 06:11:13 Breakfast Caffè Mocha Caffè Mocha Large House Blend Semi-Skimmed Warm Vanilla 16 Success ECB LTD 3 01/05/2022 06:11 06 Semi-Skimmed
Eric’s Coffee Bar West London Wandsworth ECB-WAN-OAT A800 01/05/2022 08:06:12 Breakfast Double Espresso Double Espresso Small Decaf Oatly Americano 16 Success ECB LTD 2 01/05/2022 08:06 08 Oatly
Eric’s Coffee Bar West London Wembley ECB-WEM-OAT A800 01/05/2022 08:57:11 Breakfast Cappuccino Cappuccino Large House Blend Oatly Warm Caramel 16 Success ECB LTD 3 01/05/2022 08:57 08 Oatly
Eric’s Coffee Bar West London Wembley ECB-WEM-OAT A800 01/05/2022 08:58:33 Breakfast Cappuccino Cappuccino Large House Blend Oatly Warm Caramel 16 Success ECB LTD 2 01/05/2022 08:58 08 Oatly
Eric’s Coffee Bar East London Wembley ECB-WEM-OAT A800 01/05/2022 10:43:41 Breakfast Cappuccino Cappuccino Large House Blend Oatly Warm Caramel 16 Success ECB LTD 3 01/05/2022 10:43 10 Oatly
Eric’s Coffee Bar East London Wandsworth ECB-WAN-SS A800 01/05/2022 09:53:04 Breakfast Cappuccino Cappuccino Large House Blend Semi-Skimmed Warm Caramel 16 Success ECB LTD 2 01/05/2022 09:53 09 Semi-Skimmed
Eric’s Coffee Bar West London Wandsworth ECB-WAN-SS A800 01/05/2022 10:27:46 Breakfast Cappuccino Cappuccino Large House Blend Semi-Skimmed Warm Caramel 16 Success ECB LTD 2 01/05/2022 10:27 10 Semi-Skimmed
Eric’s Coffee Bar West London Wandsworth ECB-WAN-SS A800 01/05/2022 07:17:55 Breakfast Cappuccino Cappuccino Large House Blend Semi-Skimmed Warm Caramel 16 Success ECB LTD 3 01/05/2022 07:17 07 Semi-Skimmed
Eric’s Coffee Bar East London Wimbledon ECB-WIM-SS A800 05/12/2021 05:50:18 AM Chocolate Chocolate Large Hot Chocolate Semi-Skimmed Warm Caramel 0 Success ECB LTD 2 05/12/2021 05:50 05 Semi-Skimmed
Eric’s Coffee Bar East London Wimbledon ECB-WIM-SS A800 05/12/2021 05:51:48 AM Chocolate Chocolate Large Hot Chocolate Semi-Skimmed Warm None 0 Success ECB LTD 2 05/12/2021 05:51 05 Semi-Skimmed
8

@sagmukhe Here you go sir!

9

Thank you @Steezy97 for sharing. Can you share the decafmachine1 and decafmachine2 calculations so that I can replicate it.

10

DecafMachine1 - ((((((CountDecafOatCapp+CountDecafOatChocolate+CountDecafOatLatte+CountDecafOatFW+CountDecafOatWA+CountDecafOatDbl+CountDecafOatAmericano+CountDecafOatMocha)*16)/250)/DaysInMonth)*2)*1.1)

DecafMachine2 - ((((((CountDecafSSCapp+CountDecafSSChocolate+CountDecafSSLatte+CountDecafSSFW+CountDecafSSWA+CountDecafSSDbl+CountDecafSSAmericano+CountDecafSSMocha)*16)/250)/DaysInMonth)*2)*1.1)

All of the other calculated fields like CountDecafSSChocolate are - countIf({Beverage Family}, {Beverage Family} = “Chocolate” AND MachineMilkType = “Semi-Skimmed” AND {Blend Type}=“Decaf”)

DaysInMonth - distinct_count({Local Date}) as the sheet has a filter to only show the last months’ data

11

Hi @Steezy97 , this error is interesting. Simple thing, but can you try deleting those fields in your formula and inserting them again? I wonder if any of them were deleted and then re-added with the same names, QuickSight might be holding on to the ID’s of the old versions. Your syntax looks OK to me, so I would try to just recreate this calc first.

12

Hi @Steezy97,

Since we haven’t heard back from you, assume Jesse’s suggestion resolved your issue. I’m marking Jesse’s reply as solution here. Please do let us know in case you are still facing issues with this.

Regards,
Arun