topic Re: Above Function in QlikView

Above Function

Anonymous — Thu, 14 Jul 2016 04:13:03 GMT

I was reading a blog from HIC on the Above Function, I came across the following expression:

Sum( Sales ) + Sum( {1} 0 )

I was curious about this expression, but no clue what does it Sum({1}0)mean (what does 0 represent?). And the explanation given by HIC is "to include the excluded dimension values into account"

Can someone please explain what does Sum( {1} 0 ) mean?

Thanks

Re: Above Function

Thu, 14 Jul 2016 04:41:30 GMT

{1} means disregard all the selection. Otherwise I don't get the neccessity of such an expression.

Re: Above Function

Thu, 14 Jul 2016 04:43:52 GMT

Only(Aggr(RangeSum(Above(total Sum(Amount) + Sum({1} 0),0,12)), YearMonth))

This is the one you're talking about. This is to artificially manufacture 0s and then Sum to avoid no data issue.

Re: Above Function

Thu, 14 Jul 2016 04:48:09 GMT

To demonstrate load the following data

load * inline
[
YearMonth, Amount
201601, 100
201602,200
201603,300
201605,500
201606,600
201607,700
201608,800
201609,900
201610,1000
201611,1100
201612,1200
];

Here month 4 is missing , but HIC's expression will still giving the running total.

But if you have the other one Only(Aggr(RangeSum(Above(total Sum({Amount),0,12)), YearMonth)) , it won't work. Hope that helps.

Re: Above Function

jayaseelan — Thu, 14 Jul 2016 05:01:11 GMT

Hi Karthick,

Sales:

Load * Inline

[

Customer, Sales, Brand

A, 100, B1

B, 120, B2

C, 90, B1

D, 110, B2

];

This Explanation for your understanding.

=SUM(Sales)

The above expression gives you 420 but it will change according to your selection on Customer or Brand.

=SUM({1}Sales)

The above expression gives you 420 but it will not change according to your selection on Customer or Brand.

So the answer would be 420 even after selecting any dimension

=SUM(Total Sales)

The above expression will give you Total Sales ignoring dimension but if you select any dimension, it will change accordingly. SUM(Total Sales) is useful if you want to show Total Sales against each line in Pivot or Straight Table or in any other objects.