calculated column

[Former Member]

I would like to add calculated column to an analytical view that calculates the customer level based on sales.

1. e.g. sales amount utill 1,000,000$ the customer is classified as Brones

from 1,000,000$ – 2,000,000$ customer is classified as silver.

How this can be accomplished.

Thanks,

Ronen

[rajarshi_muhuri]

Table with A as varchar [attribute] and B as integer [measure]

Creating a Calculated attribute for the measure

Creating the condition based on the calculated attribute (Unfortunately it does not work if you try to combine the earlier step together )

Finally the results

[Former Member]

I get the same error as before, any other ideas!

-Ronen

[rajarshi_muhuri]

in your first screenshot , you had the braces wrong . the second screenshot seems right .

why dont you go step by step ,

1.  first just expose that measure as a calculated attribute , and activate the odel and see if you are seeing an output . Its very important that you dont do any other operation in this step , else you will get the error like the one you got .

2. then use that calculated attribute in a IF statement , ( try with a simpler if statement to make it work first) , and then stitch your complicated nested IF .

[rajarshi_muhuri]

IF statements should work , but case statments are more suitable to use here

CASE("AMOUNT_SOLD_1", 100000-200000, 'Bronze', 200000-3000000, 'silver', 'Platinum')

[Former Member]

Hi Ronen

Can you try the below and see whether it works, I just changed some part of it .Please let us know the feedback.

  if("AMOUNT_SOLD" > 0 and "AMOUNT_SOLD" <=100000,'Bronze', if("AMOUNT_SOLD" >= 100001 and "AMOUNT_SOLD" <=200000,'Silver', if("AMOUNT_SOLD" >= 200001 and "AMOUNT_SOLD" <=300000,'Gold','Platinum')))

Thanks

Santosh Varada

[Former Member]

Hi Santosh,

The expression is working fine thanks for the more efficent version. please follow the tread with Anooj, to get an idea where is the problem.

Thanks,

Ronen

[Former Member]

Hi Ronen.

Can you show the properties window for the calculated attribute ,there might be some thing helpful with the properties or may be some number rows are restricted.

Thanks

Santosh Varada

[Former Member]

here it is:

[former_member184768]

Hi Ronen,

Do you need to implement it in the Analytic view ONLY. You can implement it in the Projection on top of Analytic view in the Calc view and it works.

Regards,

Ravi

[Former Member]

Hi,

You could use a calculated attribute as Varchar type

and use a nested if condition - something as follows (you might need to check the syntax again)

if("<sales_measure>" <= 10000000, 'Bronze', if (("<sales_measure" > 10000000 AND "<sales_measure>" < 200000000),'Silver','Gold or just space'))

Thanks,

Anooj

[Former Member]

Hi Anooj,

I tried your suggestion, see the screenshot attached, but the expression is not valid.

I suspect that the sales_measure AMOUNT _SOLD is not a valid reference in the expression.

What do you think?

-Ronen

[Former Member]

Sorry that was a bit of an oversight from me. You cannot use measures within calculated attributes it appears. Let me give it a thought of how else you can achieve this.

[Former Member]

The way around is to add your sales measure also as an attribute - you can change its name within the properties and also hide it so that you dont report on it. You can then use it to create your calculated attribute. Its a bit cheating but works

Your if statement could say if("AMOUTNT_SOLD_ATTR" < 100000,'Bronze',' ')

Thanks,

Anooj

[Former Member]

Hi Anooj,

I was able to define a valid expression:

But activation faild with the following error

SAP DBTech JDBC: [2048]: column store error: <?xml version="1.0" encoding="utf-8"?><createCubeResult version="1.0"><status><message>Inconsistent calculation model</message><errorCode>34011</errorCode></status><details><errors><detail><element>CalculationNode (finalAggregation) -> attributes -> calculatedAttribute (LEVEL) -> expression</element><code>46</code><message>Expression is not valid: Attribute engine function not implemented;TK_IF_FUNCTION<VALUE_CLASS_STRING>(TK_LE<VALUE_CLASS_INT>(TK_ID<"AMOUNT_SOLD_1", VALUE_CLASS_DECFLOAT, at 480>, TK_DECFLOAT_FUNCTION<VALUE_CLASS_DECFLOAT>(TK_CONSTANT<"100000", VALUE_CLASS_INT>)), TK_CONSTANT<"Bronze", VALUE_CLASS_STRING>, TK_IF_FUNCTION<VALUE_CLASS_STRING>(TK_AND<VALUE_CLASS_INT>(TK_GE<VALUE_CLASS_INT>(TK_ID<"AMOUNT_SOLD_1", VALUE_CLASS_DECFLOAT, at 480>, TK_DECFLOAT_FUNCTION<VALUE_CLASS_DECFLOAT>(TK_CONSTANT<"100001", VALUE_CLASS_INT>)), TK_LE<VALUE_CLASS_INT>(TK_ID<"AMOUNT_SOLD_1", VALUE_CLASS_DECFLOAT, at 480>, TK_DECFLOAT_FUNCTION<VALUE_CLASS_DECFLOAT>(TK_CONSTANT<"200000", VALUE_CLASS_INT>))), TK_CONSTANT<"Silver", VALUE_CLASS_STRING>, TK_IF_FUNCTION[here]<VALUE_CLASS_STRING>(TK_AND<VALUE_CLASS_INT>(TK_GE<VALUE_CLASS_INT>(TK_ID<"AMOUNT_SOLD_1", VALUE_CLASS_DECFLOAT, at 480>, TK_DECFLOAT_FUNCTION<VALUE_CLASS_DECFLOAT>(TK_CONSTANT<"200001", VALUE_CLASS_INT>)), TK_LE<VALUE_CLASS_INT>(TK_ID<"AMOUNT_SOLD_1", VALUE_CLASS_DECFLOAT, at 480>, TK_DECFLOAT_FUNCTION<VALUE_CLASS_DECFLOAT>(TK_CONSTANT<"300000", VALUE_CLASS_INT>))), TK_CONSTANT<"Gold", VALUE_CLASS_STRING>, TK_IF_FUNCTION(TK_GE<VALUE_CLASS_INT>(TK_ID<"AMOUNT_SOLD_1", VALUE_CLASS_DECFLOAT, at 480>, TK_DECFLOAT_FUNCTION<VALUE_CLASS_DECFLOAT>(TK_CONSTANT<"300001", VALUE_CLASS_INT>)), TK_CONSTANT<"Paltinum", VALUE_CLASS_STRING>))))</message></detail></errors><warnings><detail><element>cubeSchema</element><code>46</code><message>Default language not set. Use 'en'</message></detail></warnings></details></createCubeResult>

Thanks,

Ronen

[rajarshi_muhuri]

exactly !! by the time i finished you already posted it , and wasted my time explaining . Actually in my current work , I am doing this same thing, however i am having an added problem as its a 12 character varcar that i am having to convert to BIGINT and then chaining it to a calculation.

Rabindra : normally in sales case , it would not make any difference if you did the if condition after aggregation , however in particular case , I could not use it at a projection level , because it was already aggregated .

[Former Member]

Ronen,

I think the error is because your last If statement does not have a value on "ELSE".. change it to

if("amount" > 3000001,'Platinum','  ')

Thanks,

Anooj

[Former Member]

Thanks Anooj,

Indeed, after adding the false condition I was able to activate the view, but for some reason the expression stop at the first condition, which results in the same level for all records, I will double check this next week.

Thanks again

-Ronen

[Former Member]

Hi,

I found out that it is possible to reference the measure directly in the expression, no need to clone the measure field.

Now the calculated attribute (CUSTOMER_LEVEL) is working fine,

but when I tried to analyze the analytic view in Excel AND treat the calc attribute as a column label, only one customer record is displayed

Any idea what is wrong?

Thanks,

Ronen

[Former Member]

seems to be a problem only if you use measures directly in the expression isnt it? If you clone it as an attribute it picks up all rows as expected.

Not sure - this could be a bug or may be thats the way the olap engine deals with measures in expressions as of now which explains why they aren't available to be used within expressions. Please update here if you are able to get to the bottom of this.

Thanks

[Former Member]

Hi,

As a matter of fact, the expression is being computed correctly only if I reference the measure directly from the expression, the clone measure workaround is not working.

Thanks,

Ronen

[Former Member]

thats a bit surprsing. I used the same formula as yours with some test data and the calculated attributes (one with the measure and another with the cloned attribute) both gave me the same result when I data previewed.

Thanks,

Anooj

[Former Member]

Hi Anooj,

Here is the expression that work correctly:

if("AMOUNT_SOLD" > 0 and "AMOUNT_SOLD" <=100000,'Bronze', if("AMOUNT_SOLD" >= 100001 and "AMOUNT_SOLD" <=200000,'Silver', if("AMOUNT_SOLD" >= 200001 and "AMOUNT_SOLD" <=300000,'Gold', if("AMOUNT_SOLD" >= 300001 ,'Platinum',''))))

As you can see it directly refer to the AMOUNT_SOLD measure.

Thanks,

Ronen

[Former Member]

Hi Ronen,

In the above formula, if you had AMOUNT_SOLD_1 (the cloned attribute), are you saying the result of the expression is incorrect?

Will be good if you could have them both in separate calc attributes and attach a screenshot here of the data preview screen on the analytical view.

I used the same formula above, replaced AMOUNT_SOLD with AMOUNT_SOLD_1(cloned attribute) and I am getting same results as with the other formula. Moreover with Excel, I am getting to see all rows with AMOUNT_SOLD_1 whereas only 1 row with AMOUNT_SOLD.

Thanks,

Anooj

[Former Member]

Hi Anooj,

That's correct the results of the cloned attribute expression is not correct, all customers are Bronze.

where as the real distribution is that the majority are silver.

Thanks,

Ronen

[Former Member]

Hi Ronen,

Not sure why this is happening. On my test data, I don't see this problem.. both gives the same results. I can only suggest you to copy paste the formula that is working to the one that is not working and change the measure to the cloned attribute within it - just to eliminate any typos or silly mistakes.

Thinking about it, If Amount_Sold was genuinely an attribute, the formula should have had to work isn't it, otherwise it is definitely a bug I would think.

Could it be a revision specific problem that they are behaving differently? I am on revision 31.

Can't think of any other ideas. Sorry.

Thanks,

Anooj

[Former Member]

Hi Anooj,

Thanks for the keen effort to help!

I am using revision 28 and it could be a bug.

Best regards,

Ronen

[Former Member]

Hi Ronen,

Apologies again for misleading you with this. The reason why the cloned attribute & measure has been showing different values is because Analytical views (the OLAP engine) automatically aggregates the measures for similar attribute values. However, with your cloned attribute it wouldn't do so there by giving you different results for both in certain cases. This then makes your Customer Level formula output using the cloned attribute  wrong as well in these cases.

So you will have to stick with using the "real" measure (AMOUNT_SOLD) in your expression. However as you say you are having issues with reporting on it using Excel.

The other way out is to follow what Ravindra Channe has suggested here in this thread - create a calculated view on top of your analytical view and create the Customer Level as a calculated column within a "projection". Hopefully, that shows you all records within Excel.

Thanks,

Anooj