What I have right now
Month | Year | Sales | Store |
April | 2021 | $$$ | 0478 |
April | 2021 | $$$ | 0631 |
April | 2021 | $$$ | 0512 |
April | 2021 | $$$ | 0154 |
What I would like is
Month | Year | Sales | Store |
April | 2021 | $$$ | 0478 |
$$$ | 0631 | ||
$$$ | 0512 | ||
$$$ | 0154 |
a multi row formula tool could do this, the formula for Month for example would be:
if [Month]=[Row-1:Month] then null() else [Month] endif
then repeat that for the Year column. you'll need to make sure your data is sorted the way you want it..
Hi @Aviator0807 ,
Here's one way that you can do it
You form the date field in the first formula tool and then check if the date repeats itself with the previous row, so you only keep the first occurrence.
Hope that helps,
Angelos
@mst3k I tried that but wasnt getting the result I need.
This is what I was getting when I used that.
Month | Year | Sales | Store |
April | 2021 | $$$ | 0487 |
$$$ | 0235 | ||
April | 2021 | $$$ | 0951 |
$$$ | 0520 | ||
April | 2021 | $$$ | 0874 |
@mst3k suggestion should work if you create new fields with the multi-row formula tools, and then replace the old ones with a select tool
The issue that may arise with this approach though @Aviator0807 is that in case you have the same month but different years stacked on top of each other, that expressions will not address that change in year, as show below
Ideally this is why you should form a date field as proposed in my first post and then probably group by that field in the multi-row formula tool, before creating the logic.
Hi @Aviator0807,
I would suggest to create an ID for each group of data so that it is easier to follow through.
Assuming that your group is defined by Month and Year,
Hope this helps.
Cheers,
Lelia