topic Avg for latest 2 dates expression in QlikView

Avg for latest 2 dates expression

Tue, 12 Jan 2016 08:19:59 GMT

Hi,

I have made a table with the following:

Dimension: Name

Expression: Avg(Score)

I want to be able to count the number of people with a Score above 3.5 (scores range between 1 - 5), but the Avg should only be taken from the latest 2 dates. This means I need to incorporate the >3.5 and latest 2 dates into my expression.

Can anybody point me in the right direction?

Thanks

Mav

Re: Avg for latest 2 dates expression

tresB — Tue, 12 Jan 2016 09:55:08 GMT

May be like:

Avg({<Score={'>3.5'}, Datefield={">=Date(Max(Datefield,2)) <=Date(Max(Datefield))"}>}Score)

Re: Avg for latest 2 dates expression

sunny_talwar — Tue, 12 Jan 2016 11:03:22 GMT

I would just add one thing that if you Datefield has a different format compared to the one that is set by Date you will probably need to add the Datefield format in the Date function:

=Avg({<Score={'>3.5'}, Datefield={">=Date(Max(Datefield,2), 'YourDateFieldFormat') <=Date(Max(Datefield), 'YourDateFieldFormat')"}>}Score)

To check if the format is matching on not, just add Date(Max(Datefield)) in a text box to see if it is in the same format as DateField or not. If it is, then tresesco's solution should work as a charm, else make the slight change I just mentioned above.

HTH

Best,

Sunny

Re: Avg for latest 2 dates expression

Tue, 12 Jan 2016 12:42:08 GMT

Hi Sunny,

Thanks for help. I've tried both ways and the table doesn't display any results. I am confused as to why it doesn't show anything.

Also, when I said I wanted to calculate the score above 3.5. I meant the overall average being above 3.5 for the last 2 dates. So if Sunny had scored 3 on 12.12.2015 and 4 on 01.01.2016, the average should be 3.5 but if we had:

Alan 01.01.2016 4

Alan 03.01.2016 2

Alan 04.01.2016 5

Then Alan should not appear in the list as his average is 3 based on the latest 2 dates.

Does this make sense?

Best,

Mav

Re: Avg for latest 2 dates expression

sunny_talwar — Tue, 12 Jan 2016 17:02:01 GMT

Oh then Score cannot be used within the set analysis:

May be something on these lines:

=If(Avg({<Datefield={"$(='>=' & Date(Max(Datefield,2), 'YourDateFieldFormat') & '<=' & Date(Max(Datefield), 'YourDateFieldFormat'))"}>}Score) > 3.5, Avg({<Datefield={"$(='>=' & Date(Max(Datefield,2), 'YourDateFieldFormat') & '<=' & Date(Max(Datefield), 'YourDateFieldFormat'))"}>}Score))

Re: Avg for latest 2 dates expression

Wed, 13 Jan 2016 13:26:28 GMT

Thanks Sunny, this displays the results.

One final question. If I wanted to avoid any blank cells in the Datefield how would I do this?

for example,

Alan 01.01.2016 4

Alan 03.01.2016 4

Alan NULL CELL

This would not appear in my table, but a 4 should appear.

Do you have any idea how I can include values for this scenario?

Much appreciated for the earlier answer!

Regards

Mav

Re: Avg for latest 2 dates expression

sunny_talwar — Wed, 13 Jan 2016 13:40:49 GMT

You mean the same expression above you want to avoid blank cells? I would think that the date range within the set analysis would automatically ignore blank dates. Is it not doing that or is this a stand alone requirement where you don't have any date range set analysis, but just need to ignore blank dates?

Re: Avg for latest 2 dates expression

Wed, 13 Jan 2016 14:48:26 GMT

I think the latter. My Source table would have the below

Name	Date	Score	Status
Michael	01.01.2016	4	Complete
Michael	02.01.2016	3	Complete
Michael			Incomplete
Alan	01.01.2016	5	Complete
Alan	03.01.2016	4	Complete
Alan	05.01.2016	5	Complete
Sunny	01.01.2016	5	Complete
Sunny	02.01.2016	2	Complete
Sunny	03.01.2016	4	Complete
Mav	01.01.2016	4	Complete
Mav	03.01.2016	3	Complete
Mav			Incomplete

but in my table with the expression what is currently happening is:

Name	Average Score
Michael	3
Alan	4.5
Mav	3

Sunny would not appear as his avg is 3. Michael and Mav should have 3.5 but it only takes the last figure as I believe the average is including the blank cell. Alan is the only correct one.