topic Re: A difficult nut to crack! in App Development

A difficult nut to crack!

Anonymous — Thu, 02 Mar 2017 09:29:16 GMT

Hi Experts,

I'm trying to present data in a straight table in a certain way. Below is the table I have.

The Table has Name, Appearance Order, Date and Sale value. I want to present this in a straight table using the Appearance order. For example, the Appearance order is 3 for A, so I should be able to see only last 3 Sale and date values for Name A. I want to present this data in like this in a straight table:

Is there anyway we can do this? Either at the script level or within Qlik at expression level?

Thanks

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 10:26:49 GMT

Check this out

Dimensions

Name

=Aggr(If(Date >= Only(Total <Name> Aggr(If(Max(TOTAL <Name> Date) = Date, Above(Date, [Appearance Order]-1)), Name, Date)), Date), Name, Date)

Expression

Sum(Sale)

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 14:38:46 GMT

But 01/03/2016 is smaller than 26/06/2016? Why do you want this to show up or did you mean 01/03/2017?

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 14:40:02 GMT

For 2017 it seems to be working, but not sure I understand the logic for 2016

Re: A difficult nut to crack!

Anonymous — Thu, 02 Mar 2017 15:01:40 GMT

Apologies Sunny. It was my mistake. Please ignore my last post but I've run into another trouble. I'm attaching a file where I've just replaced the dates but for some reason Appearance Order isn't working.

This is my new table,

The result should be

But the application is showing...

All I did was change the data. Do you think this is because of date format?

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 15:06:03 GMT

I hope you are using QlikView 12 or Qlik Sense, because if you are not, then you might have to make changes within the script to make this work. For QV12 or Qlik Sense, you can try this

=Aggr(If(Date >= Only(Total <Name> Aggr(If(Max(TOTAL <Name> Date) = Date, Above(Date, [Appearance Order]-1)), Name, (Date,(numeric)))), Date), Name, Date)

Re: A difficult nut to crack!

Anonymous — Thu, 02 Mar 2017 15:09:02 GMT

Hi Sunny thanks for quick reply. Sorry I didn't understand what you mean by numeric? Can you please elaborate?

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 15:11:36 GMT

Check this link out

The sortable Aggr function is finally here!‌

This was a new functionality added in QV12 which allowed the Aggr() function to be sorted in other than just load order. Previous to QV12, Aggr() function always sorted in load order (which is what it is doing today and messing your expression).

Re: A difficult nut to crack!

Anonymous — Thu, 02 Mar 2017 15:29:46 GMT

Hi Sunny,

thanks for the link.

I just tried the same expression in Qlik Sense v 3.2 and getting the same result for some reason.

I'm using this expression

=Aggr(If(Date >= Only(Total <Name> Aggr(If(Max(TOTAL <Name> Date) = Date, Above(Date, [Appearance Order]-1)), Name, Date)), Date), Name, Date)

Do you think I'll need to change the expression?

I'll go through the link you sent me and study the new functionality. I do have slightly older version of QV but I've latest version of Qlik Sense. If it's showing me the same result in Qlik Sense as in QV then I might have to change the expression I guess.

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 15:31:28 GMT

You tried this

=Aggr(If(Date >= Only(Total <Name> Aggr(If(Max(TOTAL <Name> Date) = Date, Above(Date, [Appearance Order]-1)), Name, Date)), Date), Name, Date)

I want you to try this

=Aggr(If(Date >= Only(Total <Name> Aggr(If(Max(TOTAL <Name> Date) = Date, Above(Date, [Appearance Order]-1)), Name, (Date,(numeric)))), Date), Name, Date)

See the difference?

Re: A difficult nut to crack!

Anonymous — Thu, 02 Mar 2017 15:35:21 GMT

This is brilliant! Thanks a lot.

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 15:43:20 GMT

No problem at all

Re: A difficult nut to crack!

Anonymous — Thu, 02 Mar 2017 17:31:01 GMT

Thanks again. I've another small addition to this if you can help me...

Basically I have another Date field which contains all the same dates but also future dates meaning >today()

I want to show these future dates in new columns.

So this is my new table. Same table but one new date column.

I want to show this data in straight table like this:

So you think this is possible?

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 17:32:24 GMT

Would you be able to share the app by updating this new date column in it?

Re: A difficult nut to crack!

Anonymous — Thu, 02 Mar 2017 17:44:34 GMT

Sure. Here is the app and the data.

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 18:10:59 GMT

So to understand the logic.... if we Appearance order is 3, the planned date will be in increments of 3? Like A's appearance order is 3, you have 6 additional planned date. for B you have 2. Just trying to understand what would have happened if there were only 2 planned dates for A?

Re: A difficult nut to crack!

Anonymous — Thu, 02 Mar 2017 18:51:45 GMT

Yep! Planned dates will be in the increment of 3. So for A there are 6 planned dates. So the first three planned dates should go to Planned Date 1 Column and thee last three should go to Planned Date 2 Column in the straight table.

If A only had 3 Planned Dates then Planned Dates 2 column should be empty as there are only 3 planned dates.

Similarly for B there are 2 future planned dates which should go to Planned Dates 1 column of the straight table and Planned Dates 2 column should be empty as there are no more planned dates.

Re: A difficult nut to crack!

sunny_talwar — Thu, 02 Mar 2017 19:12:40 GMT

With slight intervention in script....

Re: A difficult nut to crack!

Anonymous — Fri, 03 Mar 2017 10:14:59 GMT

This is brilliant Sunny! Looks like this will do the trick. The only think I was thinking is if there is a way the Planned Date 1 and Planned Date 2 are sorted. For example, in Planned Date 1, 08/09/2017 is appearing before 07/09/2017.

Re: A difficult nut to crack!

Anonymous — Fri, 03 Mar 2017 11:17:50 GMT

The other thing i noticed is that if I add new data which is Name C then it doesn't add the planned dates in Planned Date 1. Not sure what wrong here...