Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
You need to use a crosstable load to unpivot your data
finally you should have only 5 columns
site_name, site_size,area,date,kwh
you can then simply do = Avg({<kwh={">0"}>}kwh)
Hi Eliran,
Please check the attached file.
Regards,
Vitalii
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
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)
Hi,
For some reason, I can't open my qvw. I will upload the QS file once I open my QVW
Regards,
Vitali
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