topic Re: How do I merge two Measure values into one dimensions (by month)? I can only do this using front-end options in QlikView

How do I merge two Measure values into one dimensions (by month)? I can only do this using front-end options

jblomqvist — Thu, 13 Oct 2016 10:47:57 GMT

Hi all,

I have a straight table in QlikView like this:

Dimension: Month

Expression 1: Aggr(Sum(LineSalesAmount), Month)

Expression 2: Aggr(Sum(LineSalesAmountForecast), Month)

(I know I don't have to aggr in the above example but in the real data I have to, the above is just a simplified version).

I would like Expression 1 and 2 to be combined into one expression so that the Sum(LineSalesAmountForecast) values are shown where you see the grey area at the moment.

Does anyone know how to do this please using front-end expressions?

I am not allowed to edit the data model.

Re: How do I merge two Measure values into one dimensions (by month)? I can only do this using front-end options

Frank_Hartmann — Thu, 13 Oct 2016 10:55:46 GMT

you could use the label names to merge the columns:

perhaps you need to modyfy your first expression like this:

if(EXPRESSION=Null(),0,EXPESSION)

in order to get it work

hope this helps

Re: How do I merge two Measure values into one dimensions (by month)? I can only do this using front-end options

sunny_talwar — Thu, 13 Oct 2016 10:57:55 GMT

May be this:

RangeSum(

Aggr(Sum(LineSalesAmount), Month),

Aggr(Sum(LineSalesAmountForecast), Month))

or this if Month is your dimension

RangeSum(

Sum(LineSalesAmount),

Sum(LineSalesAmountForecast))

Re: How do I merge two Measure values into one dimensions (by month)? I can only do this using front-end options

jblomqvist — Thu, 13 Oct 2016 12:21:57 GMT

Hi guys, both are perfectly fine working examples!

Which would be the better option performance and reliability wise?

Re: How do I merge two Measure values into one dimensions (by month)? I can only do this using front-end options

sunny_talwar — Thu, 13 Oct 2016 12:23:40 GMT

Using column() function of expression label is almost always a more efficient way to do it. The reason I gave you the RangeSum was because I thought you just needed a single expression which sums the two values up. if you need 3 column then go with franky_h79‌'s solution.

Best,

Sunny

Re: How do I merge two Measure values into one dimensions (by month)? I can only do this using front-end options

jblomqvist — Thu, 13 Oct 2016 12:32:44 GMT

Hi Sunny,

If I couldn't use the column label option, would rangesum be better than doing Expression 1 + Expression 2 method?

Re: How do I merge two Measure values into one dimensions (by month)? I can only do this using front-end options

sunny_talwar — Thu, 13 Oct 2016 12:40:02 GMT

For sure, RangeSum would be the best way to go in your case because one of the expression seems like causing nulls for you. Expression1 + Expression2 will lead to null if one of those expressions is a null. Whereas RangeSum() handles the null very well. So I almost always prefer to use RangeSum() over simple addition.

Re: How do I merge two Measure values into one dimensions (by month)? I can only do this using front-end options

jblomqvist — Thu, 13 Oct 2016 13:36:48 GMT

Perfect just as I thought, thank you