
Re: help with set analysis + expression
Stefan Wühl Aug 20, 2012 10:19 AM (in response to Carter James)You are looking for the 149 €?
Try
=sum({<Company=, Expenses={">0"}>}Expenses) / sum({<Company=, Expenses={">0"}>}FTE)
or if you want to disregard any selections:
=sum({1<Expenses={">0"}>}Expenses) / sum({1<Expenses={">0"}>}FTE)
Hope this helps,
Stefan

Re: help with set analysis + expression
Carter James Aug 21, 2012 4:58 AM (in response to Stefan Wühl )Thanks stefan!
It works on my test file, with test data.
However I realised that the moment I sum everyting that's above 0,
I would get an invalid result.
This is because the companys are often charging each other for other services,
which has an affect on the total expenses per company.
Let say that company A has made 100.000 euros expenses this year. However since they offered various of services, 30.000 is registered as a service and deducated of the 100.000 euro.
If I would do a sum({<expenses={">0"}>} expenses) company A would display 100.000 euros instead of 70.000 euros. Sum(expenses) would be the right thing to do. However, there are companys that have a total negative value.
these are the companys that need to be excluded.
I came up with a new expression: hoping to exlude the negative values per company. But that didn't work.
=money(sum({<journaalBedrag={">0"}>} Total aggr(sum(journaalBedrag), Divisie)), '€ #.##0')
What I did is first sum the expenses over the dimension Company, then using set analysis, I tried to exclud the companys that have a negative value.
but that didn't work using this expression.
I've posted a new QV file with a new data. Hopefully this would clearify the above.
Carter

test.qvw 147.0 K

Re: help with set analysis + expression
Stefan Wühl Aug 21, 2012 5:02 AM (in response to Carter James)=sum({<Company={"=sum(Expenses)>0"}>} Expenses)
resp.
=sum({<Company={"=sum(Expenses)>0"}>} FTE)

Re: help with set analysis + expression
Carter James Aug 21, 2012 6:06 AM (in response to Stefan Wühl )Hi Stefan,
Thanks for your help! That I indeed displayed the right results:
I've added company to ignore company selections. However that doesn't work. The expression seem right?
sum({<Company=, Company={"=sum(Expenses)>0"}>} Expenses) / sum({<Company=, Company={"=sum(Expenses)>0"}>} FTE)
Thanks for your help so far!
Carter

Re: help with set analysis + expression
Stefan Wühl Aug 21, 2012 6:46 AM (in response to Carter James)No, one field modifier per field in a set expression. But you can add the clearing of company like this:
=sum({<Company={"=sum({<Company=>}Expenses)>0"}>} Expenses)

Re: help with set analysis + expression
Carter James Aug 21, 2012 8:12 AM (in response to Stefan Wühl )Thanks Stefan!
That indeed did the job!!!

Re: help with set analysis + expression
Carter James Aug 23, 2012 9:47 AM (in response to Stefan Wühl )Hi stefan,
Sorry for asking you again, but im quit new to set analysis. I'm trying to add the last yearmonth.
I tried the expression below, but it doesnt work.
=sum({<Company={"=sum({<Company=, YearMonth={$(=(max(YearMonth))>}Expenses)>0"}>} Expenses)
/
sum({<Company={"=sum({<Company=, YearMonth={$(=(max(YearMonth))>}Expenses)>0"}>} FTE)
This one below doesnt work either:
=sum({<Company={"=sum({<Company=>}Expenses)>0"},
YearMonth={$(=(max(YearMonth))>} Expenses)
sum({<Company={"=sum({<Company=>}Expenses)>0"},
YearMonth={$(=(max(YearMonth))>} FTE)
Hope u can help me out!
Cheers Carter!

Re: help with set analysis + expression
Stefan Wühl Aug 23, 2012 1:55 PM (in response to Carter James)If you say, it doesn't work, what do you mean? Do you say wrong results or nothing at all? If you see wrong results, what do you expect to see?
What is the format of YearMonth? Set analysis is a bit picky here, you need to return the modifier list values in the exact same format then used for the field.
Also, you might need to apply the set expression to both sums, depending on if you want to restrict the used Companies as well as the returned Expenses by max YearMonth.
It would probably be easiest if you could expand your small sample from above to the new field, then post it again and attach also your expected outcome.
Regards,
Stefan

Re: help with set analysis + expression
Carter James Aug 27, 2012 7:16 AM (in response to Stefan Wühl )Hi Stefan,
Sorry for the vague explation. I'm trying to get the latest year month selection fixed in Qlikview, ignoring the yearmonth selection. However Qlikview does not give me the right output
I've used the expression below:
num(sum({<Company={"=sum({<Company=, YearMonth={$(=max(YearMonth))}, YearMonth=>} Expenses)>0"}>} Expenses) / sum({<Company={"=sum({<Company=, YearMonth={$(=max(YearMonth))}, YearMonth=>}Expenses)>0"}>} FTE), '€ #.##0')
If I select 201205 It gives me the the results of that month, which is some I don't want. Therefore I've added 'YearMonth=', to ignore the selection and always get the latest month regardsless of my selection.
Same goes for the lowest value:
Min({<Company=, YearMonth=, YearMonth={$(=max(YearMonth))}>} Aggr(sum({<Company=, YearMonth={$(=max(YearMonth))}>} if(Expenses>0, Expenses))
/ sum({<Company=, YearMonth={$(=max(YearMonth))}>} if(Expenses>0, FTE)),Company))
I've attached a sample.
Hope u can help!
Cheers!
Carter

testV2.qvw 150.0 K


Re: help with set analysis + expression
Carter James Aug 27, 2012 4:54 PM (in response to Stefan Wühl )Hi stefan,
I found the solution. I had to add another set analysis in the expression below:
sum({<Company={"=sum({<Company=, YearMonth={$(=max({<YearMonth=>}YearMonth))}>} Expenses)>0"}>} Expenses).
Thanks for helping me with the previous posts, I really appricate it
Cheers!
Carter






