How to calculate variable values

[wossenhh]

Hi,

I have created the following variables and when I tried to total them I don't get the correct results.

vClient = Aggr(Sum({<Client={A,B,C,D,E,F,G}>} [YTD Hours]), EmpID)

vProduct = Aggr(Sum({<Product = {AB}>} [YTD Hours]), EmpID)

vEFclient = Aggr(Sum({<Client={ABCD, EFGH, IJKL, MNOP, QRST, UVWX}>}[YTD Hours]), EmpID)

Then I created another variable for Total Hours as:

vTotalHours =$(vClient) + $(vProduct) + $(vEFclient)

So I have a straight table chart where there is a column name Total Hours and on the edit expression I put

=$(vTotalHours), however the results I am getting is not correct.

Can any body please explain what I miss here or suggest the correct methods to calculate?

Thanks,

Wossen

[MK_QSL]

Provide sample / Dummy Data..plz

[JonnyPoole]

I agree that a sample would help.  As a stab, sometimes expressions work better when you wrap the aggr() in a external sum like:

sum(  Aggr(  <chartaggregation>,<dimension>) )

You may also need to remove any preceding '=' signs from the variable definitions of the first 3 variables.

Do the individual variables work in the chart $(vClient)  ?  ...focus on getting those parts working first before you do the sum of variables variable.

[wossenhh]

Hi Manish,

Here I attached the dummy QV

[wossenhh]

Hi Jonathan,

The individual variable works in the chart. The problem I have is when I add them up using variables formula for the Total Hours. I attached the dummy sample as well.

Thanks,

Wossen

[JonnyPoole]

i switched the syntax around a bit.

Check it out

[wossenhh]

Hi Jonathan,

If you see the Client column and the Total Hours column, they have the same results, which is not correct. The Total hours should be reflect the aggregate hours for Client, Product and EF Client per Employee ID

for e.g. for Emp ID 001 the total hours should be 170, for 002 it should be 75 and so on.

Hope it is clear.

Thanks,

[JonnyPoole · Accepted solution]

oh you are totally right and i was too fast to post the final sample.  Please find attached.

The main things i needed to changed from your original sample:

remove  '=' from the beginning of all variable definitions

add  isnull checks to all variables and assign 0 if its null:    ex:    if(  isnull(  <expression>) = -1, 0, <expression> )  

Because when you add values, if any of the components is null the whole total is null too.

Try this sample please. apologies again.

[wossenhh]

Thanks Jonathan! That works well!