topic set analysis expression in QlikView

set analysis expression

Mon, 29 Aug 2011 10:22:59 GMT

Is there any reason why this expression will not work, keep getting a null value, but I know that both 'Value' and 'GP' are not null figures.

sum({$< Date_MonthYear = {">=$(#vDate12)<=$(#vDateNow)"} >} (GP/Value))

set analysis expression

Miguel_Angel_Baeyens — Mon, 29 Aug 2011 10:33:14 GMT

Brian,

If the syntax in the condition in the Set analysis is not correct or it returns strange results, the result of the whole aggregation function may be null. Check that it works without the set analysis, and if it does, you will know that the set modifier or syntax is not correct.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

set analysis expression

Mon, 29 Aug 2011 10:51:17 GMT

Thanks for the reply Miguel. It still does not work when I take out the set analyis.

sum(GP/Value) gives me a null value

sum(GP) gives me all the GP values

sum(Value) gives me all the 'Value' values

Im at a loss as to what im doing wrong, any ideas?

Thanks

set analysis expression

gandalfgray — Mon, 29 Aug 2011 10:51:23 GMT

Hi Brian

If you leave the Label of the expression empty you should be able to see the evaluation of your set expression when you hover the mouse over the column heading, this is often a good trick if you are having problems with set expressions.

set analysis expression

swuehl — Mon, 29 Aug 2011 11:17:04 GMT

Hi brianm10,

do you have values for GP and Value for each record or either GP or Value in a record?

sum(GP/Value) is calculating the division on record level, so latter will be a problem.

Maybe you could try using two sums with set expression and devide the two sums? But it depends what you want to achieve, I think GP/Value may some kind of percentage, so you might not want to calculate the sum of percentages, but the percentage of summed values anyway.

Regards,

Stefan

set analysis expression

Mon, 29 Aug 2011 11:43:19 GMT

I dont have values for each record. Some records have both a 'Value' and 'GP' figure but there are some null values.

I'm guessing this is why Im getting a null value? How can I overcome the issue?

set analysis expression

swuehl — Mon, 29 Aug 2011 15:35:35 GMT

I think even if you don't have values for each record, you should not get NULL back from your sum function. Hm, don't know why you get NULL (I assume you get NULL if you say null and not zero, right?).

But back to my last post, have you tried something like

=sum(GP) / sum(Value)

[maybe with your set expression in each sum]?

This is not the same as sum(GP/Value), but I still assume that's probably what you want if (GP/Value) is some kind of share / percentage (like profit%).

set analysis expression

Fri, 02 Sep 2011 10:55:57 GMT

Just a thought.....

I was having a very similar problem with calculating percentages. The solution to my problem was to un-select the Relative box for that column and the calculations worked.

It is probably not the solution to your problem but it might help someone "googling" for an answer to their problem.

Jason