Using a Dimension with a condition or a Calculated Dimension

[simongoodman]

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.

[PradeepK]

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 : 

[QFabian]

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

[simongoodman]

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.