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...
Forum Discussion
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
------------------------------
How would you implement the case statement?
I appreciate the feedback! I will try something.
------------------------------
Newman, Savi
------------------------------
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
------------------------------