Need to create a metric with attribute and a metric

[HTCMS]

Is that possible to create a metric to do calculation from a Attribute and a metric in MSTR?
E.g. Attribute 1 is SaleYear(datatype = integer) and Metric 1 is sum(PaidAmt)
Need to create something like (SaleYear * sum(PaidAmt)), no aggregate function needed for SaleYear.



Thanks!

 

[JaC74]

What values are stored for SaleYear? For example, 2003, 2004, etc? What would be the reason for this metric? If the SaleYear is storing years like 2003, 2003 * sum(paidamt) really doesn't provide any meaningful value.

 

[Z3]

Use Applysimple. to create a metric. The metric definition would be something like
applysimple("(#0*#1)", sum(paidamt), [saleyear])

 

[HTCMS]

Hi, Thanks for replying.

That's just an example, doesn't have an meaningful values.

I tried using applysimple("(#0*#1)", sum(paidamt), [saleyear]), but it will give me error message saying "Object (of type: Attribute) not allowed in this place."

 

[Z3]

try [saleyear]@ID whereas ID is the name of the ID form.

 

[HTCMS]

Hi, Z3
tried applysimple("(#0*#1)", sum(paidamt), [saleyear]@ID) too, same error message returned.
I also tried something like applysimple("(#0*#1)", sum(paidamt), int([saleyear])), doesn't work either, the only expression that is valid is the First function, applysimple("(#0*#1)", sum(paidamt), First([saleyear])). But it takes very long time to run the report or error message return at the end.

 

[sunnyphoenixier]

Try to create the metric this way: Sum(PaidAmt * Attribute Sales Year). It should work. But, it will calculate at the lowest Data level if that is your intention.

Phoenixier

 

[HTCMS]

using Sum(PaidAmt * Attribute Sales Year) works fine, valid expression, but not really what I want...

 

[sunnyphoenixier]

Why? Is it because the Sum is at a higher level than SalesYear Attribute? Then you can creat a view for SalesYear at the aggregated level. Of course, a view will cost you a talk with DBA.

Why do you want a metric * attribute? You may want to see if there is another way of achieving what you're trying to do.

Phoenixier

 

[HTCMS]

I need to create a linear regression line with upper and lower bound on a graph. The option that I see from MSTR is check the "show trendlines" check box, but it's only a single line. That's why I need to create a metric, 1 for the upper bound and 1 for the lower bound. The calculation is more complicated, but I just want to get the idea to see if it's possible to do.

Example will be something like:

Sales Year PaidAmt WhatINeed(SalesYear*sum(PaidAmt))
2001 100 2001*600
2002 150 2002*600
2003 200 2003*600
2001 150 2004*600

 

[nlim]

see the examples in the tutorial demo project under

public objects\reports\technical reports\reports by feature\analytics\statistics and forecasting\forecast\

There is an example of what you need.

 

[Z3]

Okay - the problem is, Your "saleyear@ID" has to conform a metric syntax. If you know your desired SQL is absolutely correct (I have some doubt), you can use the following:
applysimple("(#0*saleyear)", sum(paidamt), max([saleyear]@ID))
(you will probably get a SQL error - item selected is not in a group by clause - etc.)

If the join on attribute1 between fact and lookup is air-tight (no duplication on attribute1 ID on lookup table), you should use:
applysimple("(#0*#1)", sum(paidamt), max([saleyear]@ID))
which will be better.

Again, the root of the problem is your desired SQL. You need to have either saleyear group-byed, or you need to put an group-by function on it.