Small Challenge!

[Anonymous]

Hello Experts!

I'm trying to present data in a straight table where I need to show Name, future data and previous total average count.

My data looks like this:

So if I do a count of ID per day and overall, I should get something like this:

Now, What I want to show is a straight table that should have the Name, future dates, these average values.

it should look something like this:

Is this something we can do in Qlik?

Thanks

[sunny_talwar]

Can you post a sample here? If not what is the expression used here?

[Anonymous]

Sure! here is the data and the app.

Thanks

[sunny_talwar]

May be this:

If(Count(ID) > 0, Sum(TOTAL <Name> Aggr(Count(ID)/count(DISTINCT if(len(ID)>1, Date)),Name)))

[sunny_talwar]

Or this:

If(Count(ID) > 0, Count(TOTAL <Name>ID)/Count(TOTAL <Name> DISTINCT {<ID = {"=Len(ID) > 1"}>} Date))

[Anonymous]

Basically I need this Straight table where it shows future dates only. In the data, I have 3 future days

01/04/2017

02/05/2017

07/05/2017

for A

and 2 future days for B

17/08/2017

21/09/2017

So this chart should only show Name A and B with all the future dates

Name Future Dates Last 3 Days Average

A 01/04/2017 5.6

A 02/05/2017 5.6

A 07/05/2017 5.6

B 17/08/2017 6.33

B 21/09/2017 6.33

And total in the end which is 29.67

[sunny_talwar]

May be this

Sum(Aggr(If(Count(ID) = 0, Count(TOTAL <Name>ID)/Count(TOTAL <Name> DISTINCT {<ID = {"=Len(ID) > 1"}>} Date)), Name, Date))

[Anonymous]

Yep! This works but I now have some more data for another day for Name A.

Name	Date	Sale	ID
A	17/01/2016	600	A201
A	17/01/2016	600	A202
A	17/01/2016	600	A203
A	17/01/2016	600	A204
A	17/01/2016	600	A205

I want this to be excluded in the calculations as I only want last 3 most recent days average.

[sunny_talwar]

Not sure I follow your question.... What is the expected output now? The same as before?

[Anonymous]

Yep! The result should be the same as with the new data for Name A has now 4 distinct days and we need to calculate the average of only last 3 most recent days. The day which has been added for A is 17/01/2016 and it's the smallest date compared to these three dates already in the data:

10/06/2016

11/06/2016

12/06/2016

So it should not be included in the average calculation for A so the average should remain 5.66667