5 Replies Latest reply: Mar 22, 2017 3:03 AM by Court van de Lisdonk RSS

    Excelfile Taskforce

    Court van de Lisdonk

      The missing excelfile for my Set Analysis Problem

       

      See QVD:

      In the tab Dashboard Sales Report is a table chart and a blockchart.

       

      The blockchart is a copy of the table only design different.

       

      In the Table there are some Actual andBudget columns with expressions like:

       

      = if (company = 1, Sum(ECOwert), if (company = 2, Sum(ESBwert), (Sum(ECOwert) + Sum(ESBwert))))

       

      OR

       

      = if (company = 1, if(MarkerWorkFree = 'A',  (if(Anteil < 1, (PlanECOpT) * Anteil, PlanECOpT)), 0),

      if (company = 2, if(MarkerWorkFree = 'A', PlanESBpT, 0),

        if(MarkerWorkFree = 'A', ( (if(Anteil < 1, (PlanECOpT) * Anteil, PlanECOpT)) + PlanESBpT), 0)))

       

      These IF- statemants I wish to replace with set-analysis expressions if possible.

       

      Perhaps I first have te redesign my Excel file, I don't know. I'm open for suggestions

       

      regards

       

      Court

        • Re: Excelfile Taskforce
          Robin Hausdörfer

          May I ask, why you want to change it to set analysis?

           

          Nested IFs in 6 levels could end in a very confusing statement... I don't even know if it is possible for your example.

          Perhaps it would be easier to calculate that in the script?

          • Re: Excelfile Taskforce
            Marcus Sommer

            I think there are several things which could be (better) done different. For example by the first:

             

            pick(wildmatch(Company, 'Company1', 'Company2', '*'),

                 Sum(ECOwert),

                 Sum(ESBwert),

                 rangesum(Sum(ECOwert), Sum(ESBwert)))

             

            By the second expression might be a similar solution possible. It's not quite clear for me if the query on MarkerWorkFree is really needed - and if could it be simplified by multiplying the flag against the result by changing the flag from A to 1 and F to 0. A bit similar seems to me Anteil - could Anteil be greater than 1 - if not you might use: PlanECOpT * alt(Anteil, 1).

             

            - Marcus

              • Re: Excelfile Taskforce
                Court van de Lisdonk

                Hello Marcus,

                 

                Thanks for your solution, it works. I use a Flag also. Anteil is skipped in this table, I make some calculations in the script and create a new table on a new sheet. This is less complex and for users easier to understand

                 

                But creating a new table for Anteil forced I to exclude the influence of some filters so I rebuild your solution a little (hope you don't mind):

                 

                = (pick(wildmatch(Company, 'Company1', 'Company2', '*'),

                  Sum({$ < Year = {$(= max(Year))}, Mountinggroup=, Mounting= > } ECOValue),

                    Sum({$ < Year = {$(= max(Year))}, Mountinggroup=, Mounting= > } ESBValue),

                      rangesum(Sum({$ < Year = {$(= max(Year))}, Mountinggroup=, Mounting= > } ECOValue), Sum({$ < Year = {$(= max(Year))}, Mountinggroup=, Mounting= > } ESBValue))))

                 

                This works but I was asking myself/thinking, is there a better solution?

                 

                Regards

                 

                Court

                  • Re: Excelfile Taskforce
                    Marcus Sommer

                    I think on the chart-level won't be much possibilities to improve these logic but with changes on the datamodel it could be simplified and speed up - by generating a single Value-field which contained the right value regarding to each company. But this could mean a whole change of your datamodel and if it yet worked like expected I wouldn't change it only to optimize this expression.

                     

                    - Marcus