Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Avg for latest 2 dates expression

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

Tags (1)
1 Solution

Accepted Solutions

Re: Avg for latest 2 dates expression

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


7 Replies
MVP
MVP

Re: Avg for latest 2 dates expression

May be like:

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

Re: Avg for latest 2 dates expression

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

Not applicable

Re: Avg for latest 2 dates expression

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

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


Not applicable

Re: Avg for latest 2 dates expression

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

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?

Not applicable

Re: Avg for latest 2 dates expression

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

   

NameDateScoreStatus
Michael01.01.20164Complete
Michael02.01.20163Complete
Michael Incomplete
Alan01.01.20165Complete
Alan03.01.20164Complete
Alan05.01.20165Complete
Sunny01.01.20165Complete
Sunny02.01.20162Complete
Sunny03.01.20164Complete
Mav01.01.20164Complete
Mav03.01.20163Complete
Mav Incomplete

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

   

NameAverage Score
Michael3
Alan4.5
Mav3

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.

Community Browser