Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand

View solution in original post

15 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
Highlighted
Contributor III
Contributor III

Hi Gysbert,

Thanks for your input. I tried to understand your comment and make the expression this way:

Gysbert2.png

I am sorry when I understood it in the wrong way. There where no results in the table. Gysbert1.png

I think the problem is that when I use the field 'inspectiedatum' that there is no answer in the record what means that I can't compare it.

When I interpretated you expression wrong I really wanted to know what way it has to be.

Thanks anyway...

Highlighted
MVP & Luminary
MVP & Luminary

That's not what I meant. Can you upload a qvw document with data? See this document for instructions: Preparing examples for Upload - Reduction and Data Scrambling


talk is cheap, supply exceeds demand
Highlighted
MVP & Luminary
MVP & Luminary

Use instead Expression1 + Expression2 ... which will return Null if any from the expressions isn't valid rangesum(Expression1, Expression2, ...).

- Marcus

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

Thanks for the instructions for uploading a qvw!  very usefull, also for demonstrations

I will upload the qvw.

Highlighted
Contributor III
Contributor III

I am sorry Marcus that I don't understand exactly your explanation. I understand I have to compare 2 expressions, but I don't know the expressions.

Highlighted
MVP & Luminary
MVP & Luminary

I meant you adds several expressions like:

...

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

    +

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

...

but if only one from these expressions had a invalid result the whole expression will be NULL. If you used a rangesum() or you wrapped your expression in an alt(Expression, NumericDefaultValue) function you will be always return a numeric result:

rangesum(

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

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

....)

- Marcus