Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
surajap123
Contributor II

peek the previous

Hi Experts,


I want to take the sales of previous month, if there is no current month sales. I tried to do this using peek function, but it is not working.

In this example you can notice that Product: Pencil has no sales in Feb. So I want to pick the sales of Pencil from Jan.

Here is the complete script-

Data:
LOAD * INLINE [
Product, Date, Sales
Pen, 01/01/2017, 10
Pen, 02/01/2017, 10
Pencil, 01/01/2017, 10
Pencil, 02/01/2017, 10
Pen, 01/02/2017, 10
Pen, 02/02/2017, 10
Pencil, 01/02/2017
Pencil, 02/02/2017
]
;


AggrData:
LOAD Product as Pd,
Month(Date(Date)) as Month,
if(sum(Sales)=0,Peek(sum(Sales)),sum(Sales)) as AggrSales
Resident Data
Group by
Month(Date(Date)),Product

Please help!

1 Solution

Accepted Solutions
MVP
MVP

Re: peek the previous

Maybe like

AggrData:

LOAD Product as Pd,

  Month(Date) as Month,

    if(sum(Sales)=0, If(Peek('Pd')= Product,Peek('AggrSales')),sum(Sales)) as AggrSales

Resident Data

Group by

  Month(Date),Product

Order by Product, Date ;

7 Replies

Re: peek the previous

You want same date previous month sale or last day of previous month? If I change your example to this, what would be the required output?

Data:
LOAD * INLINE [
Product, Date, Sales
Pen, 01/01/2017, 10
Pen, 02/01/2017, 10
Pencil, 01/01/2017, 40
Pencil, 02/01/2017, 50
Pen, 01/02/2017, 10
Pen, 02/02/2017, 10
Pencil, 01/02/2017
Pencil, 02/02/2017
];

Will it be 50 and 50 for 01/02/2017 and 02/02/2017 or will it be 40 and 50?

MVP
MVP

Re: peek the previous

Maybe like

AggrData:

LOAD Product as Pd,

  Month(Date) as Month,

    if(sum(Sales)=0, If(Peek('Pd')= Product,Peek('AggrSales')),sum(Sales)) as AggrSales

Resident Data

Group by

  Month(Date),Product

Order by Product, Date ;

surajap123
Contributor II

Re: peek the previous

Thanks for the replies guys.

Stefan's solution is what I am expecting.

Just a final question. I want to connect both the aggregated table and the Data table, so that selection on any field in Data table in UI will filter the chart based on fields from aggregated table. I created the concatenated key, but throwing error-

Product & '|'& Date as key,

Re: peek the previous

The filters should flow through in there current form... It will form a synthetic key, but I don't see a reason why the filters from Data table won't work....

Re: peek the previous

Or use Month(Date) in your key

Data:

LOAD *,Product & '|'& Month(Date) as key

INLINE [

    Product, Date, Sales, Country

    Pen, 01/01/2017, 10, UK

    Pen, 02/01/2017, 10, UK

    Pencil, 01/01/2017, 10, US

    Pencil, 02/01/2017, 10, UK

    Pen, 01/02/2017, 10, US

    Pen, 02/02/2017, 10, UK

    Pencil, 01/02/2017, , US

    Pencil, 02/02/2017, , UK

    Pencil, 02/02/2017, 1, UK

];

AggrData:

LOAD Product & '|'& Month(Date) as key,

  Product as pd,

  Month(Date) as Month,

  if(sum(Sales)=0, If(Peek('Pd')= Product,Peek('AggrSales')),sum(Sales)) as AggrSales

Resident Data

Group by

  Month(Date),Product

  Order by Product, Date;

Re: peek the previous

In your aggregated table, you are grouping on Month, not on Date. The Date field doesn't exist anymore and cannot be used to create a key.

What you can do is create a Month+Product key in both the original table and in the aggregated table. That will work.

surajap123
Contributor II

Re: peek the previous

Thanks your so much to everyone for solution/suggestion and time.