Generated months using RANGE_APPEND but how to fill value using PQLExample: Lead time value changed in 2022-03 as 40 to 30 and after no change, the requirement is to generate months from 2022-04 to till now and show the 30 value to all forward m

Hi Kiran

The question is not very clear, the question gave me an impression that you wanted to use the RANGE_APPEND to generate months from 2022-04 till today but it seems like you have that covered....

What are you looking for actually ? Do you want to split your lead time for all the months generated ?

If yes shouldn't just adding RANGE_APPEND  as dimension and Lead Time in KPI give you the details ?

Thanks Abhishek, but in this case the Lead Time is non aggregate KPI, please find requirement in below screenshot, How to generate data as below in PQL not in SQL ?

Hi Kiran,

Did you try the INTERPOLATE function?

Best,

Marcel

Thanks Marcel, Yes I tried with INTERPOLATE but it did not worked, may be because here I used RANGE_APPEND to generate months backward and forward(screenshot above). if you have any example with INTERPOLATE + RANGE_APPEND please share.

Hi Kiran,

I think you could do it like this:

Table with 1 record:

OLAP table with dimension:

TIMELINE_COLUMN ( MONTHS, FROM ( {d '2022-01-01' } ) , TO ( {d '2022-12-01' } ))

And KPI:

COALESCE(

  PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."changed_to" ) ,

  LAG (PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."changed_to" )) ,

  LEAD(PU_FIRST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."changed_from" ))

)

When there is a "changed_to" value for a certain month then that value will be taken. If there is no value for "changed_to" then it will take the "changed_to" from the previous month. If this is also not available it will take the first "changed_from" value from the next month.

This gives the following result:

Best,

Marcel

Thanks Marcel, Unable to use TIMELINE functions along with direct fields from table, getting no common table found error also when used PU functions the values are not grouping correctly.

Hi Kiran,

What do you mean with "when used PU functions the values are not grouping correctly."?

I am not sure if there is a better way. but when I use:

PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."material" )

and

PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."plant" )

I get:

When you want the values for material and plant in all the rows you could use something similar as for the lead time:

for material

COALESCE(

  PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."material" ) ,

  LAG (PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."material" )) ,

  LEAD(PU_FIRST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."material" ))

)

for plant

COALESCE(

  PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."plant" ) ,

  LAG (PU_LAST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."plant" )) ,

  LEAD(PU_FIRST ( TIMELINE_TABLE ( "XX_TEST"."change_month" ) , "XX_TEST"."plant" ))

)

The result:

Best,

Marcel