Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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.
BI Consultant
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
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.
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
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?
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%).
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