Hello, I have to fill out a form with a lot of information including a price and a date. But the dates are not always added in the correct order (there may be an earlier date added after.
So I have an INDEX column that displays a number to tell me the order of these dates.
So I try to work with these numbers to find the right order but I can’t do it.
The goal is to have the dates in the correct order so that I can add the prices in relation to these dates.
And thus calculate the cumulative price based on the progress of these dates.
Indeed, I tried but I can’t do it.
The difference between your example and mine is that the dates are not in order.
In fact, the form I created is linked to a client. And on this page I have a lot of information including a date and a price. But they are not in the right order
So I made a QUERY to put all the clients in order for a LOOKUP to retrieve the dates. Then I tried your solution this morning but I couldn’t do it.
So I added a column for INDEX to have the rank number of these dates.
Then I tried your solution with that in addition but impossible…
It’s in French, I hope it goes well…
In the photo it’s almost good but the order is reversed, I don’t understand why
And it goes from $2,000,000 to $1,800,000 when it should only increase in this specific case
Yes, that’s it, the cumulative total of the value of the price (which is right next to it on this same table) given to each customer but in the order of the date assigned and not the date of addition
The earliest date is Dec 1 (row 11), so that has the lowest cumulative total.
And the latest date is March 5 (row 3), so that has the highest cumulative total.
The filter on the Query column is simply “date is on or before This row → date”
Columns 1/2/3 which respectively display 1,900,000 / 1,800,000 / 2,000,000 should be in this order 1,800,000 / 1,900,000 / 2,000,000.
The cumulative value in line 4 is 1,600,000.
To this value are added:
02/26/2024 → 100,000 therefore 1,800,000 + 100,000 = 1,900,000
03/05/2024 → 100,000 therefore 1,900,000 + 100,000 = 2,000,000
And this is not the case and it is not logical since the last one is not the largest while all the added values are positive
Why?
If they were in the order that you describe, then they wouldn’t be in date order.
This is why I am confused. You tell me you want it in date order, but when I show you how to do that you tell me that isn’t what you want
February 26 → 100,000 therefore 1,800,000 + 100,000 = 1,900,000
March 5 → 100,000 therefore 1,900,000 + 100,000 = 2,000,000
The dates are in order… that’s why I don’t understand
All this just to have a graph displayed…
But the difference is obvious
A camouflaged calculation with only positive values should only go up (and then whatever the date…)
I would like to try something else that might fix the problem.
Is it possible to sort the numbers which determine the rank of a date in a new column? I got these ranks with an INDEX
So basically, can we create a new column that classifies existing numbers out of order but this time in order?
