10 Replies Latest reply: Aug 27, 2012 4:54 PM by Carter James RSS

    help with set analysis + expression

      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
          Stefan Wühl

          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

              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
                  Stefan Wühl

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

                   

                  resp.

                   

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

                    • Re: help with set analysis + expression

                      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

                          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

                              Thanks Stefan!

                              That indeed did the job!!!

                              • Re: help with set analysis + expression

                                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

                                    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

                                        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

                                          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