Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## 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
Partner - Master

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
7 Replies
Partner - Master

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
Contributor III
Author

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.

Partner - Master

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
Contributor III
Author

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

Partner - Master

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
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.

Contributor II

Welcome to the world of Qlik, it's all about workarounds. From day 1, you will meet unnecessary limitations, but there is a great community, which has awkward workarounds for almost everything.