topic using aggr function inside variable in QlikView

using aggr function inside variable

xavieravinson — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Shall i use the Aggr function inside the Variable.I have already tried the function inside the variable. It is working on selections. with out selection it is giving overall sum of the measure.

My Variable

=Sum(Aggr(Budget,[Sales Off],Employee))

Sample Data Inline

LOAD * INLINE [

Budget, Employee, Sales Off, Sales Grp

10000, Xavier, 1001, 101

15000, Antony, 1001, 102

10000, Napolean, 1002, 103

20000, Ravinson, 1002, 104

];

with Regards

XavieRavinson

Re: using aggr function inside variable

lironbaram — Tue, 03 Jan 2017 10:13:04 GMT

what is the result you are looking for

as it's stand your function in variable will calculate the total sum of budget

but will do it by first creating a virtual table which will give less than ideal performance

inside a variable the expression sum(Budget) will give you the same results

Re: using aggr function inside variable

Anil_Babu_Samineni — Tue, 03 Jan 2017 10:14:59 GMT

How about this? You should use this variable into Set Expression only

Aggr(sum([Sales Off]),Budget, Employee)

Re: using aggr function inside variable

saimahasan — Tue, 03 Jan 2017 10:29:00 GMT

What type of output you are expecting?? What number should be calculated in the variable??

Re: using aggr function inside variable

xavieravinson — Tue, 03 Jan 2017 10:33:51 GMT

Hi Liron,

Thanks for your reply.

My question is shall i calculate aggregated values using variables. If i use the aggr() function inside the expression i am getting correct value without any selections.

If i use the expression inside the Variable it is not giving the accurate result without selection.

Re: using aggr function inside variable

xavieravinson — Tue, 03 Jan 2017 10:35:57 GMT

Hi Anil,

Sorry.Why Sum(Sales Off) I am not getting your answer.

With Regards

XavieRavinson

Re: using aggr function inside variable

xavieravinson — Tue, 03 Jan 2017 10:37:37 GMT

Hi Saima,

Looking for a result

like above without any selections.

Thank you

With Regards,

XavieRavinson.

Re: using aggr function inside variable

Anil_Babu_Samineni — Tue, 03 Jan 2017 10:45:23 GMT

That gives Data point with the Aggr of Dims

Anyhow, I've seen below reply, I used Simple Sum(Budget) to get this

Without Selection

With Selection

Re: using aggr function inside variable

MK_QSL — Tue, 03 Jan 2017 10:49:17 GMT

I don't think you need AGGR function here.

Create a Straight Table.

Dimension

Employee

[Sales Off]

Expression

SUM(Budget)

Aggr is same as Group By in script.

Aggr will create a virtual table.

Please go through below two links to get better idea of Aggr function.

Pitfalls of the Aggr function

When should the Aggr() function NOT be used?

Re: using aggr function inside variable

parimikittu — Tue, 03 Jan 2017 10:50:16 GMT

Hi, Why do you need to use aggr inside a variable to obtain above result. You can create a straight table with Dimensions(sales Grp, Employee) and measure as sum(Budget). If u still insist on using Aggr function use Aggr(sum(Budget),Sales Grp,Employee) as expression and Dimensions(sales Grp, Employee).

Re: using aggr function inside variable

xavieravinson — Tue, 03 Jan 2017 10:50:38 GMT

Hi Anil,

Thanks for the reply.

Actual data is bit different from the sample. and the data volume also very high. My question is not getting the result. Shall i use the aggr() function inside the variable with out any selection is my actual question.

With Regards,

XavieRavinson.

Re: using aggr function inside variable

Anil_Babu_Samineni — Tue, 03 Jan 2017 10:54:53 GMT

I am not sure, May be that's my fault to understand. Have you seen Manish Reply he share few links which was nice. Check those and then try to share Real data for Understanding then we may offer you even we will use the Aggr with Vaitbles.

xavier Ravinson wrote:

Shall i use the aggr() function inside the variable with out any selection is my actual question.

Can you confirm this how you created variable for aggregation and then please share which expression you used to get 100% Accuracy data?

Re: using aggr function inside variable

parimikittu — Tue, 03 Jan 2017 10:56:08 GMT

Hi Xavier, This expression( =Sum(Aggr(Budget,[Sales Off],Employee)) ) don't make sense as it is not aggregating nums, it should be Aggr(sum(Budget),[Sales Off],Employee). Here we are aggregating nums on Sales Off and Employee

Re: using aggr function inside variable

xavieravinson — Tue, 03 Jan 2017 10:59:18 GMT

Hi Anil,

My Expression Used in the Sample is:

=Sum(Aggr(Budget,[Sales Off],Employee,[Sales Grp]))

and my question is shall i use the same expression inside a variable.

With Regards,

XavieRavinson.

Re: using aggr function inside variable

Anil_Babu_Samineni — Tue, 03 Jan 2017 11:19:27 GMT

If you want to use, This as a variable then you will get like FAV(Full Accumulation Value) for each Employee like 55000 (For Ex.)

And this is not make sense to store the FAV as i mentioned before. So, further processing you may not get as achieved with the same variable which done the Expression for Variable. For below, you can use vVariable or $(vVariable). As i mentioned above, you will get 55000 for sample data for each employee.

LET vVariable = =Sum(Aggr(Budget,[Sales Off],Employee,[Sales Grp]))

HTH

Re: using aggr function inside variable

xavieravinson — Tue, 03 Jan 2017 11:24:17 GMT

Hi Vj

Sum(Aggr(Field_to_Calculate,dimension1,dimension2,dimension3....,n))

Re: using aggr function inside variable

saimahasan — Tue, 03 Jan 2017 11:32:19 GMT

If this is the expectation, then you can simply create a pivot chart with dimension Sales Grp and Employee and use sum(Budget) as expression and its done.

Re: using aggr function inside variable

xavieravinson — Tue, 03 Jan 2017 11:40:54 GMT

Thanks for your reply.

My actual data is different and data volume is very high. Sorry for this sample data that does't have much things. But my question is shall i use the same aggr function inside the variable.

It is not about the result.

Thank you,

XavieRavinson

Re: using aggr function inside variable

parimikittu — Tue, 03 Jan 2017 11:43:41 GMT

if sum is used out side Aggr function, then it will give the total value of the table instead of total per dimnesions.