topic Daily average/monthly in QlikView

Daily average/monthly

Fri, 20 Sep 2013 08:39:15 GMT

Hello,

Sorry for posting again, but apparently i do not understand this AVG good enough, so i hope someone can HELP me

First of all i need an Monthly average which is = Sum(TotalSessions) / Distinct Month

Second i need a Daily average which is sum(TotalSessions) / Distinct Days

Ive tried to load my date into Month like Month(CreateDate) as Factmonth - But my month shows jan, when actually it is september.

Im guessing i should do something similar on days, but only inserting number 1, cause then i can count where i have data.

Cause i dont want to count where i dont have data. and because each customer can have data form different dates.

Example for one of the customers - They only have 3 lines of data (but my data model has more days in that month that is selected (so i diviveds by all the dates, but it should only divived by the 3 dates where there is data, i could filter where data is bigger than 0 but thats not an option)

Attaached is a sample file

Re: Daily average/monthly

sujeetsingh — Fri, 20 Sep 2013 08:45:29 GMT

Thomas ,

Your model is too confusing can you explain that in what chart what you want?

Re: Daily average/monthly

Fri, 20 Sep 2013 09:05:41 GMT

In my Text boxes. I want daily average and monthly average.

My daily average should be

Sum(Totalsessions) / Totalnumberofdays (but only for the selected customer) - where there is data

My Monthly average should be:

Sum(TotalSessions) / Totalnumberofmonths (but only those months where the customer has data) eg. some of them has data on 2 months and some only have data on one 1 month

Re: Daily average/monthly

Fri, 20 Sep 2013 10:04:22 GMT

I know its hard to understand.

But really basic:

I should be able to show monthly and daily average. But only for those dates where there is data.

Some customers has data for jun,july,aug,sep - they should be average for 4 month

some customers has data for july,aug,sep - they should be average for 3 month

some has data for aug,sep - they should be average for 2 months.