Restricting data in an expression

[#1]

Hi All

I am trying to reduce the amount of data returned and this is determined by the data returned in the expression.

I have the following expressions which give me physical stock, weekly forecast and weeks stock holding.

I calculate the weeks stock holding by dividing physical stock by weekly forecast but I want to reduce the data returned by saying only show me weeks stock holding greater than or equally to 20.

At the moment I have got my expression as:

=sum(physicalstock)/(weeklyforecast)

I can't get my head round how I would use set analysis to only show me weeks stock holding greater than or equal to 20 or find an option in the properties of the expression to give me what I want.

Thanks in advance

Steve

[swuehl]

Not sure if set analysis is the way to go here.

In what context are you using the expression? In chart, using which dimensions?

It  would be helpful if you can add some more details about your data model, like the fields and tables involved in your specific analysis.

I assume you are looking at Weeks, so maybe something like this in a text box:

=Sum( Aggr( if(Sum(physicalstock) / only(weeklyforecast) >=20, Sum(physicalstock) / only(weeklyforecast) ), WeekField))

[#3]

Hi

The data is in a straight table. I have multiple products and I am trying to identify products where we are holding 20+ weeks of stock in our warehouse.

I have attached a spreadsheet which is a sample file of what I am presenting in my report. The code and description are dimensions, everything else is an expression.

I cannot post my qvw as this would be displaying company sensitive information.

I want to filter the weeks stock holding column so that it only show stock at greater than or equal to 20 weeks.

I guess I can just export to excel and filter there but if I could do it in QlikView it would remove a step.

Thanks Steve

[swuehl]

How do you get your weekly forecast? Is it just a field linked to code?

[#5]

Hi

Weekly forecast is the sum of June, July and Aug Fcast divided by 13 to give an average weekly forecast.

Steve

[swuehl]

If you want to suppress some lines in your straight table, there are basically two ways to do this:

a) Filter the dimension (by a calculated dimension or by using set analysis in all your expressions)

b) Return zero for these lines in all your expressions and enable 'supress zero values' in presentation tab

Latter would be quite easy if you can set up an expression that is not depending on the other expression (you can call it a Show/ Hide condition expression), that returns zero or 1, then enclose all your other expressions with something like

=if( "ShowHideExp" >0, YourExpressionComesHere, 0)

This will not work if your ShowHideExp is based / depending on the other expressions, like I think it's the case in your setting. Then it's like self-referencing loop. You would need to rebuild the condition in every single expression.

You can try a set expression to limit the code dimension, I think something you had in mind from the beginning.

This set expression is probably quite complex in your case, using an advanced search, something like

=sum(

{<Code = {"=sum(physicalstock) / (sum({SetExpressionToSelectJunToAug} forecast)/13) >=20"}>}                                  physicalstock)/(weeklyforecast)

And you would need to apply this set expression to all aggregations in your expressions (at least to aggregation functions in nominators).

Hope this helps,

Stefan

[#7]

Hi Stefan

Thanks for your help. I think this is a bit too complex for my knowledge. An export to excel and filtering before distribution will be easier. The 3 months forecast changes as this is provided with a variable and it doesn't help that I can't provide the QVW.

Thanks again.

Steve