7 Replies Latest reply: Jan 13, 2016 9:48 AM by Mavrinder Dhothar

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

• 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

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

• 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?

• Re: Avg for latest 2 dates expression

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.