3 Replies Latest reply: May 25, 2017 8:27 AM by Thales Monteiro

Get all possible periods, repeating values.

Good morning folks,

I have a situation here that I'm not getting any soluction by my own...

I have 3 main fields to do this:

the product ID "CD_PRODUTO" to identify it

the periods reference "PERIODO" formated like 'YYYYMM' that tell when some product was bought

and the cost "CUSTO" that tell how much each unit cost by that purchase.

The problems is that the most part of the products aren't purchased every month. I need to show every product, period and price, even if it hasn't been purchased by this period, and by reapeating the price of the last bought.

There are a picture of the data table.

• Re: Get all periods between to dates, repeating values.

Hi Thales

To fill in the missing dates you'll have to create a MasterCalendar on which you should join your sales figures.

You will have Null values for the dates where you did not sell a certain product. You can use the Peek and Previous functions to populate these fields with Null values.

Creating A Master Calendar

Peek() vs Previous() – When to Use Each

• Re: Get all possible periods, repeating values.

Hi,

Temp:
LOAD *,Previous(PERIODO) as P_1,Previous(CUSTO) as C_1 Inline [
CD_PRODUTO,PERIODO,CUSTO
1,201601,15
1,201606,16
1,201609,18
2,201604,25
2,201606,26
2,201610,28
]
;
Concatenate
P_1 + IterNo() as PERIODO,C_1 as CUSTO
//Date(MonthStart(Date#(P_1,'YYYYMM'),IterNo()-1),'YYYYMM') as PERIODO1 //if You need PERIODO as Date Field
Resident Temp
While  (P_1 + IterNo()+1) <= PERIODO
;

Regards,

Antonio

• Re: Get all possible periods, repeating values.

Looks like you saved the day, my friend.

Thank you.

Just for knowledge. I only used two loops following your example. The first one was like yours, to load new lines for each blank period until the next one arrives, and another, to load new lines with the last period of purchase until today, in case of no purchases in the last month.

Really thankful