Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set analysis expression

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

7 Replies
Miguel_Angel_Baeyens

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

Not applicable
Author

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

gandalfgray
Specialist II
Specialist II

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.

swuehl
MVP
MVP

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

Not applicable
Author

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?

swuehl
MVP
MVP

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%).

Not applicable
Author

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