Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,I'm trying to calculate the difference between monthly projections of sales in a table as the one below
(e.g. projection made in July 2021 of sales expected in Q1 of 2022 VS projection made in Jan 2022 of sales expected in Q1 of 2022).
I'm using a formula like the following to identify the volume difference of the projections (last projection minus older projection)
sum(aggr(sum({<[Monthly Prediction] = {'$(=MaxString([Monthly Prediction]))'}>} [Sales Qty]), [Year], [Monthly Prediction], [Product])))-sum(aggr(sum({<[Monthly Prediction] = {'$(=MinString([Monthly Prediction]))'}>} [Sales Qty]), [Year], [Monthly Prediction], [Product])))
The formula works if I take out [Monthly Prediction] field from the table.
If I leave the field in the table the system returns the same first table without performing the difference.
I would expect something like the above where the table shows the difference maintaining the [Monthly Prediction] field.
Is there a way to disregard the Monthly Prediction column in the set analysis calculation, maintaining the field in the output table?