topic Re: Re: Re: How to calculate variable values in QlikView

How to calculate variable values

wossenhh — Thu, 04 Sep 2014 14:33:09 GMT

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

Re: How to calculate variable values

MK_QSL — Thu, 04 Sep 2014 14:38:29 GMT

Provide sample / Dummy Data..plz

Re: How to calculate variable values

JonnyPoole — Thu, 04 Sep 2014 14:54:28 GMT

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.

Re: Re: How to calculate variable values

wossenhh — Thu, 04 Sep 2014 16:02:41 GMT

Hi Manish,

Here I attached the dummy QV

Re: Re: How to calculate variable values

wossenhh — Thu, 04 Sep 2014 16:04:43 GMT

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

Re: Re: Re: How to calculate variable values

JonnyPoole — Thu, 04 Sep 2014 16:51:22 GMT

i switched the syntax around a bit.

Check it out

Re: Re: How to calculate variable values

wossenhh — Thu, 04 Sep 2014 17:02:27 GMT

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,

Re: Re: Re: How to calculate variable values

JonnyPoole — Thu, 04 Sep 2014 17:49:01 GMT

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.

Re: Re: How to calculate variable values

wossenhh — Thu, 04 Sep 2014 21:03:42 GMT

Thanks Jonathan! That works well!