Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be this:
RangeSum(
Aggr(Sum(LineSalesAmount), Month),
Aggr(Sum(LineSalesAmountForecast), Month))
or this if Month is your dimension
RangeSum(
Sum(LineSalesAmount),
Sum(LineSalesAmountForecast))
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
May be this:
RangeSum(
Aggr(Sum(LineSalesAmount), Month),
Aggr(Sum(LineSalesAmountForecast), Month))
or this if Month is your dimension
RangeSum(
Sum(LineSalesAmount),
Sum(LineSalesAmountForecast))
Hi guys, both are perfectly fine working examples!
Which would be the better option performance and reliability wise?
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
Hi Sunny,
If I couldn't use the column label option, would rangesum be better than doing Expression 1 + Expression 2 method?
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.
Perfect just as I thought, thank you