Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
star_limit
Creator II
Creator II

MonthYear average

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

MonthYearAccountIDLOS
May-2016Account110
May-2016Account220
May-2016Account35
May-2016Account410
May-2016Account55

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.

1 Solution

Accepted Solutions
dmac1971
Creator II
Creator II

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

=sum(LOS)/count(DISTINCT AccountID)

View solution in original post

9 Replies
sunny_talwar

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

star_limit
Creator II
Creator II
Author

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.

sunny_talwar

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?

star_limit
Creator II
Creator II
Author

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.

Not applicable

Hi,

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

=Aggr(Avg(LOS),MonthYear)

-Joyson G

star_limit
Creator II
Creator II
Author

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.

dmac1971
Creator II
Creator II

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

=sum(LOS)/count(DISTINCT AccountID)

View solution in original post

star_limit
Creator II
Creator II
Author

Jason!

This didn't work.

Thanks.

star_limit
Creator II
Creator II
Author

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.