Filtering the Feild values from the table

[#1]

Hi,

I am trying to get the month numbers from the table based on the average sales. The condition is to display those months whoes average sales is greater than monthly sales. I am using a straight table and have taken Month as the dimension, for expressions I have taken Sum(Sales) and avg(Total Sales).

Now when I try restricting the months buy using the if condition "If(sum(Sales) > avg(Total Sales), Month)" in either Calculation Condition or in the conditional option available for the month column in the presentation tab. The result remains the same, there is not a single change.

I am not sure if I am doing this wrong. Is there a way where I can set this filter?

The data used is mentioned below.

Sales_Month:
LOAD * INLINE [
Month, Sales
1, 100
2, 250
3, 175
4, 600
5, 180
6, 240
7, 200
8, 190
9, 280
10, 300
11, 350
12, 450
]

I am using the trail version of Qlikview. When I searched this forum I did get some solutions, but even after following those solutions I am facing the same problem. Due to the trail version, I am unable to open those qlikview sample documents which are shared.

Regards,

Vinay SN

[pat_agen]

hi,

I think if you just use the following as your expression then this will work with month as dimension without using any other conditions:

if(sum(Sales)>avg(total Sales),sum(Sales))

[#3]

Yes, I was able to obtain the required months by changing the expression formula.

I found one more method, where we can add a dimension directly to get the month number the formule used was " If(Aggr(sum(Sales) < Avg(TOTAL Sales),Month),Month)", but the problem over here is we have to select the "Suppress when value Is null"

But is there any other way where we can mention the condition and restrict the data. Like the filter options which we have in business objects.

Won't the Calculation Condition in General Tab or the column conditional in the presentation tab help? If no, then when should these options be used for?

- Vinay

[pat_agen]

Hi Vinay,

firstly I work on v8.5 so my answers may not be the most up to date.

The calculation condition on the general tab governs the calculation of the chart as an object. This will be evalauated once for the whle chart and not record by record for a given dimension as you would want in your example. The calculation condition is useful when you have a large chart and would like to force your users into making some selections before running the chart. Or if running the particular chart has no sense unless certain conditions have been met.

The conditional column in the presentation tab must be post v8.5 but I would imagine that again it will hide or display the entire dimension and not certain elements of the dimension depending on the conditions set.

As for the filter object from BO I have no hands on practice of BO but do know that it is a sql generator. As such a filter object would play a part in generating the where clause of the sql query. The where clause wil restrict the rows sent back bythe query. Qv operates differently, the results in your cahrt can be affected by any selections made anywhere in the document so long as those selections operate on fields which can logically be joined back to your dimensions.

Your query however was to restrict dimensions elements depending on their value. To do this each row has to be queried anyway to check that its value was higher than the overall average. the we simply wanted to exclude those rows that failed this test. from a performance perspective the expression I gave you does this well enough as it tests the result row by row in the dimension and only shows the required answer set. you could maybe work it another way by adding a calculated dimension but I doubt there would be any performance benefits.

Hope thsi is useful and happy qliking!