topic Re: Using a Dimension with a condition or a Calculated Dimension in QlikView

Using a Dimension with a condition or a Calculated Dimension

simongoodman — Thu, 11 Feb 2021 16:49:19 GMT

I am trying to create a bar chart where Y = the Sales Amount by Company and X = the Months, based on a condition:

Company sales >=500 units needs to be found but all sales data for that company needs to be returned.

Dataset

Company	Sales	Month
A	200	1
A	500	2
A	10000	3
B	100	1
B	400	2
B	10	3
C	600	1
C	150	3

Expected Chart data

Company	Sales	Month
A	200	1
A	500	2
A	10000	3
C	600	1
C	150	3

I has thinking of using Aggr as a condition in dimension or a calculated dimension would be the solution, but I have yet to get the expected result.

I think I am making rookie mistake somewhere. I would appreciate help.

Re: Using a Dimension with a condition or a Calculated Dimension

PradeepK — Thu, 11 Feb 2021 19:50:18 GMT

Issue with this request is that you have to use Aggr on Month & company dimension and apply filter condition on >=500 sales .. but in expected result you do not want Month aggr to get all sales data..

I'll suggest you to move Month & company aggregation at script and create flag for >= 500 sales condition.

In case you cannot.. use below expression for reference.

Calculated Dimension Expression :

Aggr( only( {< [_Company] = {"$(=concat( distinct Aggr( if(sum([_Sales]) >=500, [_Company]) ,[_Company],[_Month] ) , chr(34) & chr(44) & chr(34) ))"} >} [_Company]) ,[_Company] )

Data used :

[Temp_data]: Load * Inline [ _Company _Sales _Month A 200 1 A 500 2 A 10000 3 B 100 1 B 400 2 B 10 3 C 600 1 C 150 3 ](delimiter is '\t');

Result :

Re: Using a Dimension with a condition or a Calculated Dimension

QFabian — Thu, 11 Feb 2021 20:18:44 GMT

Hi @simongoodman , did you check this properties in the bar chart ?:

Re: Using a Dimension with a condition or a Calculated Dimension

simongoodman — Fri, 12 Feb 2021 15:29:57 GMT

Thank you both, I appreciate the time and help.

I did look at the Dimension Limits but it will only return values >=500.

I am looking at the calculated dimension as an idea, although with a bigger more complicated dataset it is not happy. Also I have used a flag in the script approach with a resident load with Group By.