Formula Help

Question

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.&nbsp;I have created a report that has "if the name is ABC and the Quantity is XYZ, then show it.&nbsp;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.&nbsp;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...&nbsp;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.&nbsp;Attached is a screenshot of what the type of report I am needing is.------------------------------Newman, Savi------------------------------

miketamoush · Answer

You could make a formula checkbox, with your really long rule in it, and then use that checkbox in the report. However, I think a much better approach is to create two new fields.[Minimum quantity needed on hand] - numeric field[Is quantity on hand less than minimum quantity?] - formula checkboxCheckbox formula would be: [Quantity On Hand] &lt; [Minimum quantity needed on hand]Run through all of your sku's and manually set the minimum quantity needed on hand. Then simply use the checkbox formula in your report. This is also nice because you can use that checkbox formula in subscriptions (every monday send out a report where that is checked...etc). You can use it in reports to color code rows (highlight rows red that the quantity on hand is too small).Also, you can just run through the few hundred parts you need to track and add the minimum number. As part of your filter you can add in the rule 'Only show parts where the [Minimum quantity on hand]&gt;0, or something like that. This would limit your list just to that 100. Or you can say, only show me parts where the checkbox is checked. That really limits it.
------------------------------Mike Tamoush------------------------------

williamknoll · Answer

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 P80U114And Quantity on Hand is less than or equal to the value 5And SKU is equal to the value P80TUBV1And Quantity on Hand is less than or equal to the value 3You 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]&lt;=5, true, [SKU]="P80TUBV1" and [Quantity on Hand]&lt;=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------------------------------

savinewman1 · Answer

How would you implement the case statement?I appreciate the feedback! I will try something.&nbsp;------------------------------Newman, Savi------------------------------

savinewman1 · Answer

Thank you! I didn't think of this!&nbsp;------------------------------Newman, Savi------------------------------

williamknoll · Answer

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
------------------------------

Forum Discussion

Formula Help