Variable input slider with table

[jalanhart]

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? 

[rubenmarin]

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

[jalanhart]

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!

[rubenmarin]

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.

[jalanhart]

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

[rubenmarin]

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.

[jalanhart]

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!

[rubenmarin]

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?

[jalanhart]

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

[rubenmarin]

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