topic Re: peek the previous in QlikView

peek the previous

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: peek the previous

sunny_talwar — Mon, 27 Mar 2017 11:17:53 GMT

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?

Re: peek the previous

swuehl — Mon, 27 Mar 2017 11:34:25 GMT

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 ;

Re: peek the previous

surajap123 — Mon, 27 Mar 2017 12:15:01 GMT

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

sunny_talwar — Mon, 27 Mar 2017 12:17:34 GMT

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

sunny_talwar — Mon, 27 Mar 2017 12:18:46 GMT

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

Peter_Cammaert — Mon, 27 Mar 2017 12:18:50 GMT

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.

Re: peek the previous

surajap123 — Mon, 27 Mar 2017 12:25:30 GMT

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