can't find the correct filter with Set Analysis

[Anonymous]

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.

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!

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..

[Anonymous]

Hoi Marcus,

I understand... Rangesum is making the expressions also more clear to read. 

Finaly the score is still the avarage and not the score of the last inspection.

Jan

[Anonymous]

Hi Gysbert,

I uploaded the qvw file... You had already time to look at it?

Jan

[jopmoekotte]

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

[Gysbert_Wassenaar · Accepted solution]

Yes, I think you can use this expression:

FirstSortedValue(aggr(

(

  (

  (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

)

,DebtorNr,Inspectiedatum), -aggr(Inspectiedatum,Inspectiedatum))

[Anonymous]

Hi Job,

Thanks for your input. This script is working in dead. Finally I only added Laatste_inspection={1}

I will change to my fieldnames to see it working on my own data.

The most easy way for me is to use the last script from Gysbert but I sure will try yours to learn from it. It's a script solution.

Thanks..

Jan

[Anonymous]

with one word:  PERFECT!

Thanks for your support.

Jan