Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
simongoodman
Creator
Creator

Using a Dimension with a condition or a Calculated Dimension

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

CompanySalesMonth
A2001
A5002
A100003
B1001
B4002
B103
C6001
C1503

 

Expected Chart data 

CompanySalesMonth
A2001
A5002
A100003
C6001
C1503

 

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.

 

 

 

 

Labels (2)
3 Replies
PradeepK
Creator II
Creator II

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 : 

cal_dim_500.PNG

 

QFabian
Specialist II
Specialist II

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

QFabian_0-1613074724822.png

 

QFabian
simongoodman
Creator
Creator
Author

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.