6 Replies Latest reply: Aug 17, 2015 9:18 AM by Matt N

# Combine Calculated Dimension into Expression.

Combine Calculated Dimension into Expression.

I have a chart reporting correct data by using the following Calculated Dimension and Expresssion: I need to combine these two parts into a single expression but have run into a road block on how to combine them.

I need to sum the metric in week2 for those who had more in week2 over week1.

Working Chart:

Calculated Dimension:

=AGGR(

IF(sum({1<FieldWeekStart = {'\$(vWeek2)'},Vendor={'CompanyA'}>} \$(vMetric))

>=sum({1<FieldWeekStart = {'\$(vWeek1)'},Vendor={'CompanyA'}>} \$(vMetric)),'Growth'

)

,CompanyKey)

Expression:

sum({1<FieldWeekStart = {'\$(vWeek2)'},Vendor={'CompanyA'}>} \$(vMetric))

Message was edited by: Matt N I took a work around to get the data in the layout needed (Horizontal accumulation with two dimension.) Rangesum(Before(sum({1<FieldWeekStart = {'\$(vWeek2)'},Vendor={CompanyA'}>} \$(vMetric)),0,ColumnNo()))

• ###### Re: Combine Calculated Dimension into Expression.

try this Expression

=aggr(IF(sum({1<FieldWeekStart = {'\$(vWeek2)'},Vendor={'CompanyA'}>} \$(vMetric))

>=sum({1<FieldWeekStart = {'\$(vWeek1)'},Vendor={'CompanyA'}>} \$(vMetric)),

sum({1<FieldWeekStart = {'\$(vWeek2)'},Vendor={'CompanyA'}>} \$(vMetric))),CompanyKey)

• ###### Re: Combine Calculated Dimension into Expression.

I tried this but it returns a Null (-). There is something missing in the logic.

• ###### Re: Combine Calculated Dimension into Expression.

try this,

=IF(aggr(sum({1<FieldWeekStart = {'\$(vWeek2)'},Vendor={'CompanyA'}>} '\$(vMetric)'),CompanyKey)

>=aggr(sum({1<FieldWeekStart = {'\$(vWeek1)'},Vendor={'CompanyA'}>} '\$(vMetric)'),CompanyKey)

aggr(sum({1<FieldWeekStart = {'\$(vWeek2)'},Vendor={'CompanyA'}>} '\$(vMetric)'),CompanyKey))

• ###### Re: Combine Calculated Dimension into Expression.

IF(sum({1<FieldWeekStart = {'\$(vWeek2)'},Vendor={'CompanyA'}>} \$(vMetric))

>=sum({1<FieldWeekStart = {'\$(vWeek1)'},Vendor={'CompanyA'}>} \$(vMetric)),aggr(sum({1<FieldWeekStart = {'\$(vWeek2)'},Vendor={'CompanyA'}>} '\$(vMetric)'),CompanyKey)),0)

• ###### Re: Combine Calculated Dimension into Expression.

Hi,

Did you manage to resolve your issue? Thank you.

• ###### Re: Combine Calculated Dimension into Expression.

The provided solutions have not solved the answer. All three result in null or zero depending on the use of the if

The new screen shot is with my original solution working as a dimension and expression. The data is present and if I can get the formula working I would be able to see the total 151805.3 as a total in its own text box..