Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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()))
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)
I tried this but it returns a Null (-). There is something missing in the logic.
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))
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)
Hi,
Did you manage to resolve your issue? Thank you.
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..