Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 :
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.