topic Comparing columns directly next to each other in a pivot table, is this possible? in App Development

Comparing columns directly next to each other in a pivot table, is this possible?

CedricD — Sun, 07 Jul 2024 14:18:35 GMT

Dear all,

I have been trying to come up with a measurement where I can compare the current month with the previous month, the caveat is that it has to compare the direct previous month even if it has a null value.

I've been using

Alt(turnover)
/
After(tota FABS(turnover))-1
,0)

and this works fine, as long as the columns have a value, however, if the direct next column contains Null() it will use the next first column that contains a value.

As can be seen in the table below:
I would like to compare okt 2023 with sep 2023 and sept 2023 with aug 2023, etc. However, with the above mentioned measurement, it will compare the current value, with the next value. So it in the example table below, for example in the first row it will compare Okt - 2023 with Sep - 2023 correctly, but it will also compare Sep - 2023 to Jun - 2023 incorrectly. or in the last row, aug - 2023 with may - 2023.

Okt - 2023	Sep - 2023	Aug - 2023	Jul - 2023	Jun - 2023	May - 2023	Apr - 2023
100	150	-	-	50	100	-
-	-	50	-	-	100	-
-	-	100	-	-	50	200

I know this problem can be fixed easily by replacing the null() with dummy rows through the load script, however, i'm trying to find a solution where I don't need to add any dummy rows as dummy rows could impact the load times.

Thank you in advance,

Re: Comparing columns directly next to each other in a pivot table, is this possible?

CedricD — Tue, 09 Jul 2024 15:27:04 GMT

bump

Re: Comparing columns directly next to each other in a pivot table, is this possible?

CedricD — Fri, 16 Aug 2024 06:53:17 GMT

Anybody can help?

Re: Comparing columns directly next to each other in a pivot table, is this possible?

Dataintellinalytics — Sat, 17 Aug 2024 06:27:57 GMT

Hi,

Can you please share some sample data set or data model to understand more? do you have master calendar implemented ? because ideally Before / After function doesn't skip null values if there is an row entry available.

Based on your sample I do see July with blank data, meaning you have an entry for July but with no data.

When I tried with sample data it worked for me.

PFA, sample data and output.

Re: Comparing columns directly next to each other in a pivot table, is this possible?

CedricD — Tue, 20 Aug 2024 06:05:58 GMT

Hi there,

Thank you for your reply.

So we do have a master calendar implemented, that's why some months have null values but are still shown.

For the data sample, here is how it looks in db form:

ProductID, Amount, Date
AproductA, 100, okt 2023
AproductA, 150, sep 2023
AproductA, 50, jun 2023
AproductA, 100, may 2023
BproductB, 50, aug 2023
BproductB, 100, may 2023
CproductC, 100, aug 2023
CproductC, 50, may 2023
CproductC, 200, apr 2023
DproductD, 100, Jul 2023

AproductA = the first row in the example table
BproductB = the 2nd row in the example table
CproductC = the 3th row in the example table
DproductD = not in the example table but it just to fill jul 2023 so the logic behind is correct