topic Re: Variable input slider with table in App Development

Variable input slider with table

jalanhart — Fri, 23 Feb 2024 21:01:02 GMT

I'm trying to create a sheet where there's a slider that adjust a table based on the slider.

My table has a mix of dimensions and measures, and one of those is a measure of months of supply (calc of QTY on hand / monthly avg usage). I created a variable and a slider, but I can't figure out how to make the table adjust accordingly to only show records where the months of supply are greater than or equal to the number of months in the slider.

How do I connect the slider to the table?

Re: Variable input slider with table

rubenmarin — Sat, 24 Feb 2024 06:59:19 GMT

Hi, the slider is linked to a variable, so you need to use that variable in the expressions or calculated dimensions.

The complicated part here is that you need to add the condition in all the expressions of the table or in one dimension that has a unique value for each row.

- for expresions it could be: If([SupplyMonthsExpression]>=$(VariableName), [ColumnExpression])

- For dimension it could be: Aggr(If([SupplyMonthsExpression]>=$(VariableName), DimensionField, DimensionField)

You can try using a calculated dimension for component that uses a combination of dimensions, I'm not sur if this could work: Aggr(If([SupplyMonthsExpression]>=$(VariableName), DimensionField, DimensionField1, DimensionField2, DimensionField3)

If you take the path of the expressions you'll need to uncheck the option to 'include zero values' on add-ons section.

If you take the path of the calculated dimensions you'll need to uncheck the option to 'show null values' on the calculated dimension

Re: Variable input slider with table

jalanhart — Mon, 26 Feb 2024 20:24:47 GMT

Thanks! that's definitely in the right direction ( I went the expression route).

I ran into an two issues though --

When I set the slider to 0 or 1 month, the totals are there calculated at the top of the table, at least some of these fields should change when going from >= 0 to >= 1 for example.

The other issue is that anything more than 2 months or more totaled in the slider, the totals row shows null altogether, which is not helpful. how do I fix this?

When 0

When 1

When 2 or more

Thanks!

Re: Variable input slider with table

rubenmarin — Tue, 27 Feb 2024 07:05:43 GMT

Hi that's usually caused by a field that has more than one different value, so it returns Null() and the expression can't be evaluated. In that case you need to include the field in an aggregation function (Sum, Avg, Max...) so it returns only one value, or use an Aggr() to do the calculation for each feld value, and then use the aggregation fuction to tell Qlik what to do with all the different values.

You can post your expression, or directly split the expression in different columns to check wich one doesn't returns any value.

Re: Variable input slider with table

jalanhart — Tue, 27 Feb 2024 21:20:35 GMT

I've tried both

"sum(If([MONTHS OF SUPPLY]>=$(vMTHSSUPPLY), [VALUE ON HAND])) "

and

"aggr(if( [MONTHS OF SUPPLY]>=$(vMTHSSUPPLY), sum(distinct INV_VALUE)),CONTRACT, COMPONENT_PART, [COMPONENT DESC]) "

and neither of them are showing the total row either.

if there are two distinct values it sums them up already for the table, so I would think that the total row is just a sum of the fields below. very frustrating! 😞

Re: Variable input slider with table

rubenmarin — Wed, 28 Feb 2024 07:17:29 GMT

Maybe it's the $(vMTHSSUPPLY), have you tried to set an expression with only this? It it returns a different value for each month it would not be calculated for the totals, as the epxression wouldnt know wich of the values has to use.

In this case an aggr would be needed, like: Sum(Aggr([Expression], MonthField)) In this case it will split the expression for the differnt months, using th value it has for each month, and then sum all the results by month.

Re: Variable input slider with table

jalanhart — Wed, 28 Feb 2024 07:28:24 GMT

I set up the variable with just a definition of 0 so that the slider would input a number.

months of supply is just a measure that takes the quantity of inventory on hand and divides it by the average monthly usage of that product. so it's not an over time factor or field, just measures based on dimension's of the product's location and ID number.

thanks for your continued help!

Re: Variable input slider with table

rubenmarin — Wed, 28 Feb 2024 07:31:03 GMT

Ok, maybe it's the other part of the expression: [MONTHS OF SUPPLY]

Or can you upload a sample with some dummy data that demonstrates the issue?

Re: Variable input slider with table

jalanhart — Wed, 28 Feb 2024 07:38:26 GMT

was wondering that too. months of supply is a juicy one because a product could have zero usage in the last 6 months and I can't divide by zero, so my measure for months of supply is this here:

if(isnull([MONTHLY AVERAGE USAGE]), 0,
IF(AVG_6MTH_USAGE = 0 AND QOH > 0, 99,
IF(AVG_6MTH_USAGE = 0 AND QOH = 0, 0,
IF(AVG_6MTH_USAGE = 0 AND QOH < 0, 0,
[QTY ON HAND] / [MONTHLY AVERAGE USAGE]))))

Re: Variable input slider with table

rubenmarin — Wed, 28 Feb 2024 07:47:53 GMT

Hi, it's the same noted above: if this can't be calculated in a row with more than one month (line it happens on the totals row) you'll need to change the expression to use aggregation on every field or use Aggr to split the calculation by the different months and then use an aggregtion formaula for tal this values, like in the example above: Sum(Aggr([Expression], MonthField))

Re: Variable input slider with table

jalanhart — Wed, 28 Feb 2024 07:58:59 GMT

OK I think i might be getting somewhere finally! I had tried the some aggregating before with no success but I tried -- sum(aggr(If([MONTHS OF SUPPLY]>=$(vMTHSSUPPLY), [VALUE ON HAND]),CONTRACT, COMPONENT_PART))

I put that in a KPI and that seems like it's working closer to how I need it. I will try to put that in my table in later (it's midnight here) and let you know how it works!

Thanks again!

Re: Variable input slider with table

jalanhart — Thu, 29 Feb 2024 16:13:42 GMT

so far this seems to be working like I need so thanks for your help!

about the slider, when you move the slider and it filters data shown in a table for example, is it actually filtering the data that's loaded like a filter visual would be doing? So the slider manipulates one field in table A, can that action filter the data in table A to adjust the data in associated table B ?

Re: Variable input slider with table

rubenmarin — Fri, 01 Mar 2024 07:09:57 GMT

Hi, I think that the only way to do that is to add the condition in each expression in both tables, or use a calculated dimension that limits the dimension values and uncheck the option to show null values in that dimension.