topic User defined field for aggregation in QlikView

User defined field for aggregation

Wed, 23 Sep 2009 21:00:39 GMT

We are writing a report that calculates weighted absolute accuracy.

I currently have similar formula to calculate difference.

Sum(if(aggr(Fabs(Sum(if([Code]='00',([Dmd]-[Dmd Plan])))),Date,[Comm])>aggr(Fabs(Sum(if([ Code]='00',([Dmd])))),Date,[Comm]) ,aggr(Fabs(Sum(if([Code]='00',([Dmd])))),Date,[Comm]),aggr(Fabs(Sum(if([Code]='00',([Dmd]-[Dmd Plan])))),Date,[Comm])))

However, we would like to make this formula dynamic where users can choose aggregation at any level. In the above formula, aggregation is at Date and Comm level. Is it possible to define variables in script that can be used in report to let users to pick their own aggregation level ?

User defined field for aggregation

Wed, 23 Sep 2009 21:21:21 GMT

Yes, you should be able to set up variables for this purpose. For example, you could use:

Sum(if(aggr(Fabs(Sum(if([Code]='00',([Dmd]-[Dmd Plan])))),$(vAgg1),$(vAgg2))>
aggr(Fabs(Sum(if([ Code]='00',([Dmd])))),$(vAgg1),$(vAgg2)) ,
aggr(Fabs(Sum(if([Code]='00',([Dmd])))),$(vAgg1),$(vAgg2)),aggr(Fabs(Sum(if([Code]='00',
([Dmd]-[Dmd Plan])))),$(vAgg1),$(vAgg2))))

And then define vAgg1 as Date and vAgg2 as [Comm] and it should calculate as before the change. You can even replace longer sections of the expression with variables, just use $(VarName) in place of that section the expression.

User defined field for aggregation

Thu, 24 Sep 2009 20:21:48 GMT

Thank you NMiller but i guess i am way behind in creating Variable in Load sript.

Let's say I have a product hierarchy table. In that table the lowest form is at SKU level, then Prod then Comm. and the highest level is div.

Table Name = Hierarchy

Div	Comm	Prod	SKU	Actl Qty	Demand Plan
AA	ABC	ABCD	11111	100	900
AA	ABC	ABCD	11112	200	800
AA	ABC	DDK	11113	300	700
AA	BBC	DDDD	11114	400	600
AA	BBC	DDDD	11115	500	500
AA	BBC	CCCC	11116	600	400
AA	BBC	CCCC	11117	700	300
AA	DDD	AAAA	11118	800	200
AA	DDD	BBBB	11119	900	100

How do i create a variable in load script to allow users to select aggregation level of their own choice of hierarchical level?

I have tried using following but did not work.

Let Var= ('DIV','comm','Prod','SKU'),'Hierachy'));

Thank you .

User defined field for aggregation

Mon, 28 Sep 2009 21:15:44 GMT

Are you trying to allow your user to be able to select one of those fields and then have the chart display for the selected field? If so, you should be able to use a Cyclic Group for this purpose. The Cyclic Group basically allows you to define multiple dimensions and then lets the user select the one they would like to use.

If you can't use a Cyclic Group and you really want to give the user a listbox with each of your levels in it, then you need to load those in a separate field. I would use an Inline Load and give the field a unique name, so it is not linked to any of your other data. Something like:

LOAD * INLINE [
 Select1
 Div
 comm
 Prod
 SKU
];

Then you could set up a variable with the value of: =GetFieldSelections(Select1) or =ONLY(Select1). The variable could then be used in your expressions.