Two-year gap in sales with a label

SilviyaK — Mon, 16 Jan 2023 16:28:51 GMT

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!

Re: Two-year gap in sales with a label

SerhanKaraer — Thu, 19 Jan 2023 20:31:30 GMT

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;

topic Two-year gap in sales with a label in App Development

Two-year gap in sales with a label

Re: Two-year gap in sales with a label