calculate average for month by days values

[EliranMoshe]

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?

[vinieme12]

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-c...

finally you should have only 5 columns

site_name, site_size,area,date,kwh

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

[vchuprina]

Hi Eliran,

Please check the attached file.

Regards,

Vitalii

[EliranMoshe]

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

[vinieme12]

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)

[vchuprina]

Hi,

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

Regards,

Vitali

[vchuprina]

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