topic Re: calculate average for month by days values in App Development

calculate average for month by days values

EliranMoshe — Mon, 04 Apr 2022 10:34:35 GMT

hi,

see xls file attached

i need to calculate the whole month average only for "Area_Three" Sites (Sites 8-14)

but exclude the "0" Values.

it's means that calculate the average of every day column with if "<>0" and another average for the results?

Re: calculate average for month by days values

vinieme12 — Mon, 18 Apr 2022 08:12:19 GMT

You need to use a crosstable load to unpivot your data

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-cross-tables.htm

finally you should have only 5 columns

site_name, site_size,area,date,kwh

you can then simply do = Avg({<kwh={">0"}>}kwh)

Re: calculate average for month by days values

vchuprina — Mon, 18 Apr 2022 11:42:27 GMT

Hi Eliran,

Please check the attached file.

Regards,

Vitalii

Re: calculate average for month by days values

EliranMoshe — Wed, 27 Apr 2022 13:52:07 GMT

hi thanks a lot

but i use Qlik Sense Hub and i can't load QVW file , can you upload other file ? on share screen?

many thanks

Re: calculate average for month by days values

vinieme12 — Wed, 27 Apr 2022 14:40:21 GMT

As Below

temp:
CrossTable(Date,Kwh,3)
LOAD
F1 as sitename,
F2 as sitesize,
F3 as sitearea,
"44621",
"44622",
"44623",
"44624",
"44625",
"44626",
"44627",
"44628",
"44629",
"44630",
"44631",
"44632",
"44633",
"44634",
"44635",
"44636",
"44637",
"44638",
"44639",
"44640",
"44641",
"44642",
"44643",
"44644",
"44645",
"44646",
"44647",
"44648",
"44649",
"44650"
FROM [lib://AttachedFiles/area_Prod (1).xlsx]
(ooxml, embedded labels, table is Sheet1)
Where len(F3);

NoConcatenate
Main:
Load
sitename
,sitesize
,sitearea
,Date(Num#(Date,'#####'), 'YYYY-MM-DD') as Date
,Kwh
Resident temp
Where isnum(sitesize);
Drop table temp;

exit Script;

In Chart

Measure = Avg({<Kwh={"<>0"} , sitename={"site 3"}>}Kwh)

Re: calculate average for month by days values

vchuprina — Wed, 27 Apr 2022 14:44:42 GMT

Hi,

For some reason, I can't open my qvw. I will upload the QS file once I open my QVW

Regards,

Vitali

Re: calculate average for month by days values

vchuprina — Wed, 27 Apr 2022 16:11:51 GMT

Hi,

Please check the attached file.

You can switch on the 'show totals' switcher to see the average for different dimensions, in the attached file you can see all possible totals for your dataset.

Regards,

Vitalii