topic Re: Combine Calculated Dimension into Expression. in QlikView

Combine Calculated Dimension into Expression.

Fri, 14 Aug 2015 17:15:54 GMT

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.

Kushal_Chawda — Fri, 14 Aug 2015 17:21:59 GMT

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.

Fri, 14 Aug 2015 20:30:27 GMT

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

Re: Combine Calculated Dimension into Expression.

Kushal_Chawda — Sat, 15 Aug 2015 04:50:41 GMT

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.

sasiparupudi1 — Mon, 17 Aug 2015 06:49:38 GMT

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.

qlikviewwizard — Mon, 17 Aug 2015 07:24:52 GMT

Hi,

Did you manage to resolve your issue? Thank you.

Re: Combine Calculated Dimension into Expression.

Mon, 17 Aug 2015 13:18:29 GMT

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..