Hello everybody.
How can I go about creating a pivot table where each column corresponds to the sales value on a certain date.
The dates are not later so I cannot apply Set Analysis formulas for a pivot table like:
Sum ({<Date = {"$ (= date (max (Date) - 0, 'DD / MM / YYYY'))"}>} Sales) First column
Sum ({<Date = {"$ (= date (max (Date) - 1, 'DD / MM / YYYY'))"}>} Sales) Second column
Sum ({<Date = {"$ (= date (max (Date) - 2, 'DD / MM / YYYY'))"}>} Sales) Third column
.. and so on.
I managed to create a table that contains the columns as shown in the example:
Date, DatePrec, NumDay, Sales
31-09-2022 24-09-2022 6 125
24-09-2022 21-09-2022 3 141
21-09-2022 13-09-2022 8 105
13-09-2022 08-09-2022 5 115
I tried replacing the value -1 with the value NumDay. For instance:
Sum ({<Date = {"$ (= date (max (Date) - 0, 'DD / MM / YYYY'))"}>} Sales) First column
Sum ({<Date = {"$ (= date (max (Date) - NumDay, 'DD / MM / YYYY'))"}>} Sales) Second column
But it does not work.
Can anyone help me.
Thank you all.