topic Re: Small Challenge! in QlikView

Small Challenge!

Anonymous — Mon, 06 Mar 2017 16:55:23 GMT

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

Re: Small Challenge!

sunny_talwar — Mon, 06 Mar 2017 16:56:41 GMT

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

Re: Small Challenge!

Anonymous — Mon, 06 Mar 2017 16:58:32 GMT

Sure! here is the data and the app.

Thanks

Re: Small Challenge!

sunny_talwar — Mon, 06 Mar 2017 17:03:55 GMT

May be this:

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

Re: Small Challenge!

sunny_talwar — Mon, 06 Mar 2017 17:06:35 GMT

Or this:

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

Re: Small Challenge!

Anonymous — Mon, 06 Mar 2017 17:17:57 GMT

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

Re: Small Challenge!

sunny_talwar — Mon, 06 Mar 2017 17:30:29 GMT

May be this

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

Re: Small Challenge!

Anonymous — Mon, 06 Mar 2017 17:39:05 GMT

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.

Re: Small Challenge!

sunny_talwar — Mon, 06 Mar 2017 18:04:51 GMT

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

Re: Small Challenge!

Anonymous — Mon, 06 Mar 2017 18:44:38 GMT

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

Re: Small Challenge!

sunny_talwar — Mon, 06 Mar 2017 19:26:29 GMT

Try this:

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

Re: Small Challenge!

Anonymous — Tue, 07 Mar 2017 08:50:52 GMT

That's brilliant Sunny. Can this functionality be achieved if I have the future dates in another column? I've attached the new data... as you can see now the future dates are in separate column.

Re: Small Challenge!

sunny_talwar — Tue, 07 Mar 2017 12:00:00 GMT

Why do you incrementally add your requirement brother ... PFA...

Re: Small Challenge!

Anonymous — Thu, 09 Mar 2017 15:24:13 GMT

Hi Sunny,

There is a problem with this solution. Whenever I select a future date my straight table doesn't show me any data. I tried {<FutureDate=>} but don't think this is working. Any ideas?

Re: Small Challenge!

sunny_talwar — Thu, 09 Mar 2017 15:26:16 GMT

Where exactly did you tried adding this? -> {<FutureDate=>} Can you share the expression you tried?

Re: Small Challenge!

sunny_talwar — Thu, 09 Mar 2017 15:31:12 GMT

May be try this:

Sum({<[Future Dates]>}TOTAL <Name>

Aggr(If(Count({<[Future Dates]>}ID) = 0,

Sum({<[Future Dates]>} TOTAL <Name>

Aggr(If(Rank(Only({<ID = {"=Len(Only({<[Future Dates]>}ID)) > 1"}, [Future Dates]>} Date)) < 4, Count({<[Future Dates]>}ID)), Name, Date))/

Count({<[Future Dates]>}TOTAL <Name>

Aggr(If(Rank(Only({<ID = {"=Len(Only({<[Future Dates]>}ID)) > 1"}, [Future Dates]>} Date)) < 4, Only({<[Future Dates]>}Date)), Name, Date))), Name, Date))

Re: Small Challenge!

Anonymous — Thu, 09 Mar 2017 15:34:43 GMT

Yep this works but the problem is, it doesn't filter straight table. Even after selecting 07.05/2017, straight table is showing all dates.

Re: Small Challenge!

sunny_talwar — Thu, 09 Mar 2017 15:36:42 GMT

Multiply expression with Avg(1)

Sum({<[Future Dates]>}TOTAL <Name>

Aggr(If(Count({<[Future Dates]>}ID) = 0,

Sum({<[Future Dates]>} TOTAL <Name>

Aggr(If(Rank(Only({<ID = {"=Len(Only({<[Future Dates]>}ID)) > 1"}, [Future Dates]>} Date)) < 4, Count({<[Future Dates]>}ID)), Name, Date))/

Count({<[Future Dates]>}TOTAL <Name>

Aggr(If(Rank(Only({<ID = {"=Len(Only({<[Future Dates]>}ID)) > 1"}, [Future Dates]>} Date)) < 4, Only({<[Future Dates]>}Date)), Name, Date))), Name, Date)) * Avg(1)

Re: Small Challenge!

Anonymous — Thu, 09 Mar 2017 15:56:43 GMT

This works well but the overall sum isn't correct. Once I've selected 17/08/20017 the total should be 6.33 not 12.00.

I guess I'll have to try and think of a different way to use set analysis here.

Re: Small Challenge!

Anonymous — Thu, 09 Mar 2017 15:57:42 GMT

or I'm thinking maybe i need to change my data model to accommodate this.