Two-year gap in sales with a label

[SilviyaK]

Hi all,

I need ideas for the below:

I have a field with seller name, sales amount and the year in which the sales happened. So basically it looks like this: 

Seller                                    Sales                               Fiscal Year

Seller1                             123 456                                 2019
Seller1                             44 444                                   2022
Seller1                             32 333                                   2023
Seller2                            324 567                                 2019
Seller2                             11 234                                  2020
Seller2                             123 564                                2023

You can see that in the example each seller has a gap of two years in their sales. Seller1 doesn't have any sales in 2020 and 2021 and Seller2 doesn't have any sales in 2021 and 2022. Everything I tried just shows me the years in which they have sales and excludes the rest because the sales are null.

I need a script that will recognize this gap of 2 years in the sales and will assign a label "no sales" to the specific fiscal year so I can have a chart where I can see the seller, the year and the label. 

Any help will be much appreciated!

[SerhanKaraer · Accepted solution]

Hello Silviya,

You can use previous function and while loop to check and fill the gaps between rows as below:

LOAD Seller, if("Previous Fiscal Year" + IterNo() < "Fiscal Year", 'no sales', Sales) as Sales, "Previous Fiscal Year" + IterNo() as "Fiscal Year"
	while "Fiscal Year" - IterNo() >= "Previous Fiscal Year";
LOAD Seller, Sales, "Fiscal Year", if(RecNo() = 1 or Seller <> Previous(Seller), "Fiscal Year" - 1, Previous("Fiscal Year")) as "Previous Fiscal Year";
LOAD * INLINE [
Seller, Sales, Fiscal Year
Seller1, 123 456, 2019
Seller1, 44 444, 2022
Seller1, 32 333, 2023
Seller2, 324 567, 2019
Seller2, 11 234, 2020
Seller2, 123 564, 2023
];

exit script;