peek the previous

[surajap123]

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!

[sunny_talwar]

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?

[swuehl · Accepted solution]

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]

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,

[sunny_talwar]

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

[sunny_talwar]

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;

[Peter_Cammaert]

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]

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