Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

waqqas2426
Contributor

The Average Problem!

Hello experts!

I've run into an interesting average problem and any help from you guys will be highly appreciated.

Basically I have 5 columns. Day, Date, Future Date, ID and service. For each ID we provide different services and record the date we provided service. We usually provide service consecutively and sometimes there are different dates. We flag this as Day 1, Day 2.

Also in Future, for each ID a future date is assigned. I want to calculate the average for each ID per day basis.

Lets have a look at the table first.

tab1.PNG

Now these are the future dates:

                     Days           Future Date ID

107/01/2017A
208/01/2017A
113/02/2017B
215/02/2017

B

So these dates and ID should be in straight table and when no future date is selected then this should be the average calculated:

tab2.PNG

But when the user selects 07/01/2017, as this date is assigned to Days 1 then only Days one past dates are used to calculate average.

Average = (4+10)/2

             = 7

tab3.PNG

Similarly, if the user selects 15/02/2017  and as this date is assigned to Days 2 then only days 2 dates for this ID is used for average calculations.

Average: = (5+10) / 2
              = 7.5

tab4.PNG

I know it's a bit tricky but this is where I need your help! I've attached both the app and data file if anyone is willing to help. Thanks

Tags (2)
1 Solution

Accepted Solutions

Re: The Average Problem!

Its me again (you are going to start hating me), try this:

=Sum(Aggr(If([Future Date] > 0, Count(TOTAL <ID> {<[Future Date], Day = p(Day)>} Service)/Count(TOTAL <ID> {<[Future Date], Day = p(Day)>} DISTINCT Date)), ID, [Future Date]))

No Selection

Capture.PNG

07/01/2017 selected

Capture.PNG

15/02/2017 selected

Capture.PNG

View solution in original post

3 Replies

Re: The Average Problem!

Its me again (you are going to start hating me), try this:

=Sum(Aggr(If([Future Date] > 0, Count(TOTAL <ID> {<[Future Date], Day = p(Day)>} Service)/Count(TOTAL <ID> {<[Future Date], Day = p(Day)>} DISTINCT Date)), ID, [Future Date]))

No Selection

Capture.PNG

07/01/2017 selected

Capture.PNG

15/02/2017 selected

Capture.PNG

View solution in original post

waqqas2426
Contributor

Re: The Average Problem!

‌Heyy Sunny

if you get a chance, can you have a look at this?

The date problem!

Thanks

waqqas2426
Contributor

Re: The Average Problem!

Hey Sunny!

The average problem continues...

I've just posted a discussion Tricky Average! which is a bit of an extension of my other discussion. Would you be able to have a look at it please?

Thanks