Multiplying two fields in a expression!

[#1]

Hi

Imagine if Date is there are as a dimension in line chart and I want some to have some below in the expression. Is it possible?

Sales * Count(ID) //Where Sales and ID are the fields.

I tried something same like above but it is not giving any value. Is there is anyother way of doing it please.

Hope some can help me out.

[kaushiknsolanki]

Hi,

     Try this as expression in your pivot table.

     sum(aggr(sum(X),X,D,E) * aggr(count(distinct E),X,E,D))

Regards,

Kaushik Solanki

[#3]

Thanks Kaushik.............i dnt have words to thank you

hats off to you buddy

keep in touch

[kaushiknsolanki]

Hi,

     My pleasure.

Regards,

Kaushik Solanki

[#5]

One more query Kaushik, i've a master table having say departments & transaction table,now i want to set up incremental update on the transaction table having some timestamp as a pkey, same named timesatmp key also exist in the master tabe dept. currently i'm taking data from the qvds of the two tables. Now if in future some dept has to be added in the master then how to account all this in the incre. load

Plz help

[kaushiknsolanki]

Hi,

     I didnt get your requirement clearly..

     Please open a new discussion and explain me their with example.

Regards,

Kaushik Solanki

[#7]

Hi Kaushik,

There's another one for you Sir, i need to create a table in the format attached.

The dim 2 has 3 categories Dim2(a),Dim2(b),Dim2(c) & The Dim3 has 3 categories one of wich is not used in this report(leave it, call it Dim3(c)), 1st is Dim3(a), for wich we have expr 1 & expr 2(calculalted only for Dim3(a)) & 2nd is Dim3(b) for wich again exprr1 & expr2 are to be calculated.

The problem is

1) the format of the report wich has dim 2 & dim 3 at the same level of pivot table whne the dim is dragged & dropped upwards so as to resemble this

2)  calclation of expr2 for if (Dim3=Dim3(a)) & for Dim3=Dim(b)

u can also call Dim3(a),Dim3(b) etc. as x,y,z

[#8]

Hello Sir,

I've a new problem of calculating differences of two cols based on some condition.

I've two tables: 1^st  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,
Dim3,
     January,
     February,
     March,
     April,
     May,
     June,
     July,
     August,
     September,
     October,
     November,
     December

     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……

Plz help

Thanks & Regards

[#9]

Hello At!

have you tried the basic?

If you ar looking for this:

"If try that as per your suggestion, where Sales=306 and ID=1244.

There result should be 306 * 1244 = 380664 but where as if I try as per your suggestion it is giving 14845896."

306 * 1244 = 380664  then use SUM(Sales*ID)

Hope this is what you are looking for!

Regards