Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

How can i change the calculated on measure

Hi Guys

 

How can I change the limitation on the dimensions to be calculated on a field that I specify? I want to use a calculated measure to limit the dimension?

 

Thanks in advance 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Hi Derick,

Great, that makes things easier! What you can do is ranking all the customers by their YTD sales + My week sales, create a ranking and applymap this.

I'll put it in concepts first since you are familiar with scripting:

  1. First get a table with your Customer ID, Sales YTD and My Week Sales and sum this. The result is a table with one row for each customer with their total sales. For this you can use a group by function.
  2. Then use a preceding load with a sort desc on your Sales column and add a new field based on RowNo(). The result is that you have the same table, but now with a column that will be sorted from high to low (Sales) and a corresponding number. Customer with highest sales will have number 1, second is number 2 etc..
  3. Now you can applymap with number to your fact table.

If you have any questions, just let me know!

Jordy

Climber

 

Work smarter, not harder

View solution in original post

6 Replies
Highlighted
Partner
Partner

Hi Derick,

Do you want to do this based on a new created measure in the front end? 

If you want to do this you need to create a measure and return a single value (example: Sum(Amount)).

Jordy
Climber

Work smarter, not harder
Highlighted
Contributor
Contributor

Hi Jordy

 

No, I want to limit my dimension based on a calculation of my YTD sales + My week sales and only pick the top 100 from that

or I would like the chart to be ranked by top 100 customers according to their sum(week sales + to week target)

I do not want to show this calculation in the bar chart. 

Highlighted
Partner
Partner

Hi Dereck,

There are some solutions for this. Are you familiar with the script editor? This would be the best and most safe solution.

Jordy

Climber

Work smarter, not harder
Highlighted
Contributor
Contributor

Hi Jordy

 

Yes, I am familiar with the script editor, What would you suggest me to do? 

 

Could I hardcode the limitation? or maybe make a synthetic selection in the background? 

 

Thank you for your time

Highlighted
Partner
Partner

Hi Derick,

Great, that makes things easier! What you can do is ranking all the customers by their YTD sales + My week sales, create a ranking and applymap this.

I'll put it in concepts first since you are familiar with scripting:

  1. First get a table with your Customer ID, Sales YTD and My Week Sales and sum this. The result is a table with one row for each customer with their total sales. For this you can use a group by function.
  2. Then use a preceding load with a sort desc on your Sales column and add a new field based on RowNo(). The result is that you have the same table, but now with a column that will be sorted from high to low (Sales) and a corresponding number. Customer with highest sales will have number 1, second is number 2 etc..
  3. Now you can applymap with number to your fact table.

If you have any questions, just let me know!

Jordy

Climber

 

Work smarter, not harder

View solution in original post

Highlighted
Contributor II
Contributor II

wow... that feels like a very convoluted way to do it... is there any way that Qlik can just do this natively? i mean, if it's supposed to be so powerful, why does the limitation parameter feel so... limited...

Is there any way to just specify an expression and a number of lines, so the highest 5 values of "expression"?

sorry if i sound so bitter... i am a programmer used to having full control in a database language and the way you do stuff in Qlik feels really backwards from normal logic.