The case might be a bit more difficult since you want the AND condition of the quantity, but you could utilize a few variables here.
What I recommend is creating a variable within the actual formula to retrieve the quantity and return a text value that sort of describes it. For example, you could add an IF condition that would return "Above5" if the quantity is above 5. For this, I'd recommend using nested IF statements over case.
Next you'd probably create a variable that would combine the SKU and quantity variable just mentioned. From there, you could create a case statement that would have a scenario for each. For example:
var text quantity = If([Quantity]>5,"Above5"[Quantity]<5,"Below5","")
var text combinedtext = [SKU]&"#"&$quantity;
var bool filtertest = Case($combinedtext,"ABC123#Above5",true,"ABC123#Below5"),true,false;
$filtertest
Although you may be able to use case here, it may be better to use some nested IF statements instead.
------------------------------
William Knoll
------------------------------
Original Message:
Sent: 05-22-2023 16:23
From: Savi Newman
Subject: Formula Help
How would you implement the case statement?
I appreciate the feedback! I will try something.
------------------------------
Newman, Savi
Original Message:
Sent: 05-22-2023 14:15
From: William Knoll
Subject: Formula Help
Hi Savi,
If you are running into the filter limit, since you seem to be setting hard values in the filter, you may be able to create some formula checkbox fields in the table (not the report).
You can then put your filter criteria here and and combine one or all of them. For example, if you originally had a filter combination on the report for:
SKU is equal to the value P80U114
And Quantity on Hand is less than or equal to the value 5
And SKU is equal to the value P80TUBV1
And Quantity on Hand is less than or equal to the value 3
You could create a formula checkbox field on the table with those relative IF conditions to set itself to true if that record has either of those SKUs and Quantities.
So it'd be like this:
If([SKU]="P80U114" and [Quantity on Hand]<=5, true, [SKU]="P80TUBV1" and [Quantity on Hand]<=3, true, false)
(You could also use a case statement for more efficiency with larger sets of values/filters)
Afterwards, you can create a report with a single filter (or more depending on how many formula checkbox fields you create) that retrieves any records where that checkbox is set to true.
------------------------------
William Knoll
Original Message:
Sent: 05-22-2023 12:15
From: Savi Newman
Subject: Formula Help
Ok,
I have been trying to build a report formula and am struggling to find the right "formula".
I feel it's a unique situation as google, the formula builder and support haven't been much help.
I have created a report that has "if the name is ABC and the Quantity is XYZ, then show it.
However, I've run out of filter options.
I need to do this for roughly 100 filter parameters.
We use THOUSANDS of parts. But I only need to see the most commonly used.
Example:
"Sku is equal to the value P80U114 AND the Quantity on hand is equal to or less than 5"
or
"Sku is equal to the value P80TUBV1 AND the quantity on hand is equal to or less than 3"
etc...
we have many many skus we need data on. But the only formulas I can find is "If ABC is XYZ AND DEF is UVW then CHANGE Value to LMNOP"
I don't need to change values, I just need to see them.
Attached is a screenshot of what the type of report I am needing is.
------------------------------
Newman, Savi
------------------------------