Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;