Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mansoorsheraz
Contributor III
Contributor III

Average of last 3 values of dimension

Hi,

I need to make a table where by i want my dimension to have the averages for last 3, last 5 and last  7 values. If the count of dimension is less than the required number of values it should display "NA" e.g. if count is less than 7 for a dimension it should display as "NA" for that dimension. I preferably need this to work in load script but open for suggestions.

mansoorsheraz_0-1610644704814.png

The screenshot above is the scenario that i am, talking about. Any help is appreciated.

Regards.

Labels (3)
13 Replies
Ksrinivasan
Specialist
Specialist

hi,

use this measure in straight table

=count({$<Rank1= {">4"}, Score={">99"}>}Player)

ksrinivasan

mansoorsheraz
Contributor III
Contributor III
Author

Thanks but this is not what i was looking for in the load script. I wanted the resultant values for the averages to be there in the load script so that they came in as pre calculated values and only drop them on the visualization will do the job. Hope you get me. I have tried using the subfield in the load script but its not working.

mansoorsheraz
Contributor III
Contributor III
Author

Never mind @Ksrinivasan I have been able to figure it out in the load script using the GROUP BY Clause. I will get back to you if i come up with this ever demanding task 🙂

Ksrinivasan
Specialist
Specialist

hi,

LOAD
Player,
Score,
AutoNumber(RowNo(),Player) as Rank1
FROM [lib://REPORT EXTRACTION SSSS1.xlsx]
(ooxml, embedded labels, table is Criket);

TTT:
LOAD *,(if(match(Rank1,1,2,3),(Score))) as AVG_3,
(if(match(Rank1,1,2,3,4,5),(Score))) as AVG_5,
(if(match(Rank1,1,2,3,4,5,6,7),(Score))) as AVG_7


Resident [Criket]
Order by Player,Rank1 Asc;

ksrinivasan