topic Re: MonthYear average in QlikView

MonthYear average

Anonymous — Wed, 08 Jun 2016 13:26:51 GMT

I am having issues getting the right average for the month for customer's length of stay. Here is the example.

MonthYear	AccountID	LOS
May-2016	Account1	10
May-2016	Account2	20
May-2016	Account3	5
May-2016	Account4	10
May-2016	Account5	5

So the average for May-2016 should be 50/5 = 10. I am using the sum(LOS)/Count(AccountID) but it is not working right. When I use "Sum" function from Chart properties, the right number gets displayed on top of the LOS column, which in this case will be 50. Even count of AccountID displays as 5. So I don't understand why sum(LOS)/Count(AccountID) does not give me the right average. After investigating further by adding a separate straight table, I see Count(AccountID) is giving me the right number but sum(LOS) is a huge number. May be adding the LOS for whole period instead of just he selected month.

I am using calendar template so I pick the MonthYear from a horizontal bar on top. Example, I pick May-2016 or any other month and the average should change automatically.

Thanks.

Re: MonthYear average

sunny_talwar — Wed, 08 Jun 2016 13:33:54 GMT

Would it be possible to share a sample to look at?

Re: MonthYear average

Anonymous — Wed, 08 Jun 2016 14:32:17 GMT

I can try later to get a sample but may be this can help to clarify. What may work is to get sum of LOS based on AccountID then divide by Count(AccountID)? What I am doing right now is just doing sum of LOS not basing of the AccountID so may be it picks up all the AccountID instead of just for the selected period. Let me see if I can reproduce a sample. Thanks.

Re: MonthYear average

sunny_talwar — Wed, 08 Jun 2016 14:35:21 GMT

Why would it pick all the AccountID? Is the Month field not linked to the AccountID in your application? If they are linked, I would assume that the Sum(LOS) should only display based on your selection. May be talk a little about your data model?

Re: MonthYear average

Anonymous — Wed, 08 Jun 2016 14:45:03 GMT

AccountID and LOS is linked to the month field. Here is the solution I can use for now. When I selected "Average" from chart properties, it displays the right average on top on the LOS column.

I will still try to generate a sample later when I get little free.

Thanks.

Re: MonthYear average

Wed, 08 Jun 2016 14:51:22 GMT

Hi,

Clearly i am not understand your scenario. Try this. It may be help

=Aggr(Avg(LOS),MonthYear)

-Joyson G

Re: MonthYear average

Anonymous — Wed, 08 Jun 2016 15:11:17 GMT

I created a sample and it works fine but it won't work on my real app. I guess I will just leave it here since it seems I am wasting my time on it and it is not even that important.

LOAD * Inline [

MonthYear, AccountID, LOS

May-2016, Account1, 10

May-2016, Account2, 20

May-2016, Account3, 5

May-2016, Account4, 10

May-2016, Account5, 5

];

Thanks.

Re: MonthYear average

dmac1971 — Wed, 08 Jun 2016 15:14:49 GMT

This works for me based on your data table at the top:

=sum(LOS)/count(DISTINCT AccountID)

Re: MonthYear average

Anonymous — Wed, 08 Jun 2016 15:31:15 GMT

Jason!

This didn't work.

Thanks.

Re: MonthYear average

Anonymous — Wed, 08 Jun 2016 15:32:34 GMT

dmac1971!

This works on the sample but not on my actual app. That's alright though since I am now not using set analysis function but just setting the average from chart properties like below.