15 Replies Latest reply: Mar 27, 2015 9:57 AM by Jan van Betuw RSS

    can't find the correct filter with Set Analysis

      First time for me here in the Community. I hope some one can help.

       

      Situation:

       

      We are doing inspections in stores. For different objects they get a score. After finishing the inspection each store get a final score. After a while there will be more inspections in de database for each store, so also more final scores for each store.

       

      Te image is showing the Order, (Inspection)date, Store and Final Score.

      qv1.jpg

       

      Calculating the final score is depending on the score of the objects. I use next formule:

      (

          (

          (count({$<ScoreID={1,4} ,Description={'Hoofdeis'}>}ScoreName)*5)

          +

          (count({$<ScoreName={'Voldoet niet'} ,Description={'Hoofdeis'}>}ScoreName)*-5)

          +

          (count({$<ScoreID={1,4} ,Description={'Eis'}>}ScoreName)*3)

          +

          (count({$<ScoreName={'Voldoet niet'} ,Description={'Eis'}>}ScoreName)*0)

          )

       

      /

       

          (

          (count({$<ScoreID={1,2,4} ,Description={'Hoofdeis'}>}ScoreName)*5)

          +

          (count({$<ScoreID={1,2,4} ,Description={'Eis'}>}ScoreName)*3)

          )

          *100

      )

       

       

      So far so good...

       

      The next table I want to show for each store the Final Score of the last inspectiondate. So when we have done 3 inspections in the same store, I only want to show a record with the last inspection an the final score of that inspection of that store

       

      Problem in this table is that its showing only the final score of (really) the last inspectiondate in the whole selection and I can't resolve it!

       

      qv2.jpg

       

      For the final score I use know the next calculating:

       

      (

          (

          (count({$<Inspectiedatum={"$(=Max(Inspectiedatum))"}, DebtorNr=, ScoreID={1,4} ,Description={'Hoofdeis'}>}ScoreName)*5)

          +

          (count({$<Inspectiedatum={"$(=Max(Inspectiedatum))"}, DebtorNr=, ScoreName={'Voldoet niet'} ,Description={'Hoofdeis'}>}ScoreName)*-5)

          +

          (count({$<Inspectiedatum={"$(=Max(Inspectiedatum))"}, DebtorNr=, ScoreID={1,4} ,Description={'Eis'}>}ScoreName)*3)

          +

          (count({$<Inspectiedatum={"$(=Max(Inspectiedatum))"}, DebtorNr=, ScoreName={'Voldoet niet'} ,Description={'Eis'}>}ScoreName)*0)

          )

       

      /

       

          (

          (count({$<Inspectiedatum={"$(=Max(Inspectiedatum))"}, DebtorNr=, ScoreID={1,2,4} ,Description={'Hoofdeis'}>}ScoreName)*5)

          +

          (count({$<Inspectiedatum={"$(=Max(Inspectiedatum))"}, DebtorNr=, ScoreID={1,2,4} ,Description={'Eis'}>}ScoreName)*3)

          )

          *100

      )

       

      The results I want to see looks so simple to build, but I am doing something wrong!

       

      Who can help me to resolve my problem?  Thanks..

        • Re: can't find the correct filter with Set Analysis
          Marcus Sommer

          This couldn't work in this way. You created with $(=Max(Inspectiedatum)) an adhoc-variable which could only have one value for the whole chart. Such variables will be calculated first and then applied to all rows.

           

          It's difficult to say how it could be solved but I would tend to read the max-date within the script, maybe:

           

          Load store, max(Inspectiedatum) as maxDate Resident xyz group by store;

           

          and use this within the expression or as a dimension-filter.

           

          As an alternatively you could split tthe expression in a if-part and the set analysis part, like:

           

          if(Inspectiedatum=Max(Inspectiedatum), (count({$<ScoreID={1,4} ,Description={'Hoofdeis'}>}ScoreName)*5) +++

           

          - Marcus

            • Re: can't find the correct filter with Set Analysis

              Hi Marcus,

               

              I understand what you mean with "could only have one value for the whole chart"  That was making my problem clear. I tried the alternatively expression:

              Marcus2.png

               

              The results:

              Marcus1.png

              It was clear for me that the records without result could not compare the 'inspectiedatum' (more then 1 inspection) with the 'Max(Inspectiedatum)'   The records with results where the records with 1 inspectiondate.

               

              So I tried to understand your description about the script. I was not sure about the command 'Resident'  but finaly I had a new field in the same Table named 'MaxDate'  filled with the data of te last inspectiondate of the store. The data was correct, but also in the table it was showing again the AVG results of each store and not the result of the last  inspectiondate.

               

              Finaly it gaves me the idea that I needed a new field in the same table which only showes the last date when it of the store when it was really the last date of inspection. So I combined a field STORE (DebtorNr)  with the last inspection date:

              Ons1.png

               

              The results in the table was showing only the MaxDate when it was a record from the MaxDate.  (It also could be a Y(es)  or N(o))

              Ons2.png

               

              In the expression I used the field 'MaxDate' and filtered on ISNULL records or "" records

              Ons3.png

               

              With the results in te Table:

              Ons4.png

              Of course I want to know if there is a much more easier way.... but finaly I have the good results...

               

              Thanks for your input....

            • Re: can't find the correct filter with Set Analysis
              Gysbert Wassenaar

              Try: FirstSortedValue(aggr( ...original_expression_here... ,DebtorNr,Inspectiedatum), -Inspectiedatum))

              • Re: can't find the correct filter with Set Analysis
                Jop Moekotte

                Hey Jan,

                 

                I read your original post and thought you might want to solve this in a completely different way, so here we go.

                What I attempted to do (with some dummy data) is to create a simple and effective solution in the script.

                 

                here is the script, which should obviously be editted to your exact scriptcode and fields:

                 

                [Data]:
                LOAD *,
                  debtor &'_'& datum as Key
                ;
                LOAD *,
                  date([inspection date]) as datum
                ;
                LOAD * INLINE [
                    Order, debtor, inspection date, ScoreID
                    5000, 1, 12-1-2014, 1
                    5000, 1, 12-1-2014, 2
                    5000, 1, 12-1-2014, 3
                    5000, 1, 12-1-2014, 4
                    5001, 1, 20-5-2014, 2
                    5001, 1, 20-5-2014, 4
                    5002, 2, 21-5-2014, 1
                    5002, 2, 21-5-2014, 2
                    5002, 2, 21-5-2014, 3
                    5002, 2, 21-5-2014, 4
                    5003, 3, 25-5-2014, 1
                    5003, 3, 25-5-2014, 2
                    5003, 3, 25-5-2014, 3
                    5003, 3, 25-5-2014, 4
                    5004, 2, 30-8-2014, 1
                    5004, 2, 30-8-2014, 2
                    5004, 2, 30-8-2014, 3
                    5004, 2, 30-8-2014, 4
                    5005, 4, 31-10-2014, 1
                    5005, 4, 31-10-2014, 2
                    5005, 4, 31-10-2014, 3
                    5005, 4, 31-10-2014, 4
                ];
                
                
                [Temp]:
                NoConcatenate LOAD DISTINCT
                  debtor as debiteur,
                  datum as tempdatum
                RESIDENT Data;
                
                
                [Temp2]:
                left join (Data)
                LOAD *,
                  debiteur &'_'& tempdatum  as Key
                ;
                NoConcatenate LOAD *,
                  if(debiteur=Peek(debiteur,-1),0,1) as Laatste_inspection
                ;
                LOAD * RESIDENT Temp Order By debiteur, tempdatum DESC;
                drop table Temp;
                drop fields debiteur, tempdatum;
                

                 

                In the table you can now just add Laatste_inspection={1} to the set statement.

                 

                hope this helps.

                 

                Kind regards,

                Jop