Solved: Re: How can i change the calculated on measure - Qlik Community

DerickMulder · ‎2019-04-29

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

JordyWegman · ‎2019-04-29

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:

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

JordyWegman · ‎2019-04-29

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

DerickMulder · ‎2019-04-29

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.

JordyWegman · ‎2019-04-29

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

DerickMulder · ‎2019-04-29

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

JordyWegman · ‎2019-04-29

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:

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

QlikkitySplits · ‎2020-01-23

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.

Tooor · ‎2020-12-09

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.