Can calculated dimensions be used as conditions?

[#1]

For example:

A	B	C	D	result
< 5K	< 4K	< 2K	< 1K	6%
≥ 5K，and< 10K	≥ 4K，and< 7K	≥ 2K，and< 3K	≥ 1K，and< 2K	7%
≥ 10K，and< 15K	≥ 7K，and< 10K	≥ 3K，and< 4K	≥ 2K，and< 3K	9%
≥ 15K，and< 22K	≥ 10K，and< 15K	≥ 4K，and< 5K	≥ 3K，and< 4K	11%
≥ 22K	≥ 15K	≥ 5K	≥ 4K	13%

Above is the rule.

If I have some data like this:

Type	Amt
A1	2000
A2	6000
A3	200000
A4	1000
B1	3000
B2	5000
C1	1000
C2	4500
C3	6000
D1	500
D2	1500
D3	3500
D4	4500

And have already grouped by functions:

Type	Group	Amt
A1	A	2000
A2	A	6000
A3	A	200000
A4	A	1000
B1	B	3000
B2	B	5000
C1	C	1000
C2	C	4500
C3	C	6000
D1	D	500
D2	D	1500
D3	D	3500
D4	D	4500

How can I have the result?(Use many if functions?)

Type	Group	Amt	result
A1	A	2000	6%
A2	A	6000	7%
A3	A	200000	13%
A4	A	1000	6%
B1	B	3000	6%
B2	B	5000	7%
C1	C	1000	6%
C2	C	4500	11%
C3	C	6000	13%
D1	D	500	6%
D2	D	1500	7%
D3	D	3500	11%
D4	D	4500	13%

[SergeyMak]

Hi,

If you have same intervals like in case of  D and C, where interval 1k, you can use Class() function.

So you can use IF to calculate depending on group and Class to divide by intervals

[#3]

So there is no easier way to deal with A and B?

[SergeyMak]

As I know only IF...

But I always move these calculations to the load script and have no problem..

In load script you can additionally use predefined intervals in the separate table and use intervalmatch function to assign this classification. But it's better if you need to give the user the possibility to change these intervals without you.

[#5]

Sergey,

if you need to resolve rules on the fly, you may:

transform you ranges table into formula during reload:

Re: pick rating on the fly using calculated score

use expression like in color definition here:

Re: Re: Color Code based on Inline Table utilizing two dimensions in that table

You may also try build your lookup using firstsortedvalue().

regards

Darek

[SergeyMak]

Thanks for sharing, Dariusz.

Very interesting

[#7]

Thank you, Sergey and Dariusz.

But I have one more question:

Can calculated dimensions be used for the conditions of expression?

Actually the rule of group is much more complicated(not just use left function),and when I use multiple "IF",I have to copy and paste the "group" formulas many times.Is there an easier way to get the problem solved?

[SergeyMak]

Hi Joy,

Usually I move the conditional logic from the expression to the load script and model and then use simple expressions like sum(Value). For example I usually put budget and actual data to one table with additional column Type. Or do the same if I have several types like orders, invoices, payments etc.

share a sample of data and I will try to explain ho to do it

because it's really depends on the data and the requirements.

[#9]

HI ,Sergey

Thanks for the help. If I share the data, I have to translate many Chinese expression into English.Or You will be difficult to understand the requirements.Can I use variable or set expression to do that?

[SergeyMak]

As I told before I usually move conditional logic to the script

but actually everything depend on the requirements