I've a new problem of calculating differences of two cols based on some condition.
I've two tables: 1st having dimensions as Dim 1--> Dim 2-->Dim 3.....they've hierarchial relation as we represent in a pivot table.
But here we're concerned only wid Dim 3….the expr is Exp 1…Table1.xlsx is attached
Now the other is an excel file in crosstable format having sheets named 2011 & 2012 (Years) having
Cols Dim 3 & expr2…Table2.xlsx is attached….the script is below
Crosstable(Month,expr2,2)LOAD $(sheetname) as Year,
FROM '$(file)' (ooxml, embedded labels,table is $(sheetname));
I’m fetching them in QV & need to calculate the third expression : expr3 as
Expr3=if(expr1-expr2 for feb 2012>0,30 (expr1/expr2)*30)
Calculate Expr4 if Expr3=30: Expr4= if(expr1-(expr2 for feb 2012 + expr2 for Jan2012)>0,30,(expr1-expr2 for Feb2012)/(expr2 for Jan 2012)*30)
Calculate Expr5 if Expr4=30: Expr5=if (expr1-(expr2 for feb 2012+expr2 for Jan2012 +expr2 for Dec2011)>0,30,(expr1-( expr2 for feb 2012+expr2 for Jan2012))/(expr2 for Dec 2011)*30
And so on……till expr3/expr4/expr5 is less than 30...and as soon as we get any of them less than 30 then add another col showing the sum:
for eg….expr3=30,expr4=30 and expr5=29, so Sum col=expr3+expr4+expr5
where & how shud this logic be put……in script or in GUI expressions……
Thanks & Regards