Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

waqqas2426
Contributor

A difficult nut to crack!

Hi Experts,

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

Tab1.PNG

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:

Tab2.PNG

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

Thanks

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: A difficult nut to crack!

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?

21 Replies
MVP
MVP

Re: A difficult nut to crack!

Check this out

Capture.PNG

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)

MVP
MVP

Re: A difficult nut to crack!

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?

MVP
MVP

Re: A difficult nut to crack!

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

Capture.PNG

waqqas2426
Contributor

Re: A difficult nut to crack!

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,

tab4.PNG

The result should be

tab5.PNG

But the application is showing...

tab6.PNG

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

MVP
MVP

Re: A difficult nut to crack!

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)

Capture.PNG

waqqas2426
Contributor

Re: A difficult nut to crack!

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

MVP
MVP

Re: A difficult nut to crack!

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

waqqas2426
Contributor

Re: A difficult nut to crack!

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?

tab7.PNG

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.

MVP
MVP

Re: A difficult nut to crack!

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?