Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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.
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 .
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.