Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
May be like:
Avg({<Score={'>3.5'}, Datefield={">=Date(Max(Datefield,2)) <=Date(Max(Datefield))"}>}Score)
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
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
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))
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
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?
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.