topic Re: help with set analysis + expression in QlikView

help with set analysis + expression

Mon, 20 Aug 2012 13:49:07 GMT

Hi Guys,

I have 2 reference lines in my bar chart. The one displays the minimum expeses per FTE the other the average expenses over all companys.

I created the minimum reference using the following expression:

= Min({<Company=>} Aggr(sum({<Company=>} if(Expenses>0, Expenses))

/ sum({<Company=>} if(Expenses>0, FTE)),Company))

as you can see the company field selection is ignored.

Now I'm trying to get the total average of all the companys fixed as a refference line. But I find it difficult to generate the right expression.

I've attached a qv sample what I would like to achieve

Hope any one can help!

Cheers

Carter

Re: help with set analysis + expression

swuehl — Mon, 20 Aug 2012 14:19:26 GMT

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

Tue, 21 Aug 2012 08:56:09 GMT

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

Re: help with set analysis + expression

swuehl — Tue, 21 Aug 2012 09:02:10 GMT

=sum({<Company={"=sum(Expenses)>0"}>} Expenses)

resp.

=sum({<Company={"=sum(Expenses)>0"}>} FTE)

Re: help with set analysis + expression

Tue, 21 Aug 2012 10:06:13 GMT

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

swuehl — Tue, 21 Aug 2012 10:46:53 GMT

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

Tue, 21 Aug 2012 12:12:48 GMT

Thanks Stefan!

That indeed did the job!!!

Re: help with set analysis + expression

Thu, 23 Aug 2012 13:47:09 GMT

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

swuehl — Thu, 23 Aug 2012 17:55:29 GMT

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

Mon, 27 Aug 2012 11:16:04 GMT

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

Re: help with set analysis + expression

Mon, 27 Aug 2012 20:54:54 GMT

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