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]

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))

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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

[Anonymous]

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?

[sunny_talwar]

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

[sunny_talwar]

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))

[Anonymous]

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.

[sunny_talwar]

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)

[Anonymous]

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.

[Anonymous]

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