Pivot table expression based on dimension value

[#1]

Hi all,

I have the following pivot table:

I have two expressions:

1. Schedules Snapshot: These represents the total schedules volumes for Quarter 2 as they appeared in different 'Week'.

2. Actual Sales

The highlighted row in the pivot tables above is the first week of Quarter 2 for year 2017. As you can see, there is a drop in schedules for Quarter 2, from week 31 onward, as these schedules are turned into actual sales.

The challenge is in the Actual Sales expression. I want this expression to represent the sales performed on that quarter until the 'Week' dimension. i.e. there should be no sales before week 201631, and from this week onward the sales should increase with an inversely proportional manner to the decrease in the schedules.

At the moment it is showing the total volume sales performed for the quarter. I can understand this as I know that expression are calculated once for the entire chart. However I read that you can use an 'if' statement in the expression to achieve what I am looking for. But until know this did not work for me. This is the current expression that I am using for the Actual Sales:

= SUM(TOTAL <FiscalYear, FiscalQuarter> InvoiceQuantityShipped)

I am using the 'TOTAL <FiscalYear, FiscalQuarter>' part to ignore the 'Week' dimension which is only related to the schedules.

I attached the application with the pivot table above.

Any suggestions of how this can be achieved will be greatly appreciated

Thanks,

Matt

[rubenmarin]

Hi Matthew, I dont' know how to identify wich week will be the reference week so I fixed 201631.

If you want to accumulate scheduled snapshot differences you can use this expression:

=If(ScheduleCaptureWeek>=201631, RangeSum(Alt(Above(ThisColumnName), 0), Above(Sum(ScheduleQuantity_historic)))-Sum(ScheduleQuantity_historic))

201631: It shouldn't be a fixed number, or if it's fixed use a variable

ThisColumName: Use the same name as the expression label

[#3]

Hi Ruben,

Thanks for your reply.

However, this is not what I am looking for. The differences in schedules snapshots does not directly correspond to the actual sales. In fact I have to different QVDs for the actual sales and the schedules.

[ElizaF]

My guess is that mistake comes from how it is made the connection between the tables: "Finance Fact" and "HistoricSchedules"

I recommend you create a "link table" between this two tables.

The week 20160331 appears in the all fiscal years and quarters. See the atached image.

[rubenmarin]

Not sure of the objective... set a percentage of the total sales of the quarter based on the decrease % in Schedule?

Maybe?:

=If(ScheduleCaptureWeek>=201631,

Sum(TOTAL <FiscalYear, FiscalQuarter> InvoiceQuantityShipped)

*((Sum(TOTAL <FiscalYear, FiscalQuarter> {<ScheduleCaptureWeek={201631}>} ScheduleQuantity_historic))-Sum(ScheduleQuantity_historic))/Sum(TOTAL <FiscalYear, FiscalQuarter> {<ScheduleCaptureWeek={201631}>} ScheduleQuantity_historic))

[#6]

That is completely right Eliza, not a mistake in my data. This means that with the schedule snapshot that was taken on 201631, schedules can be found in all fiscal years and for all quarters.

i.e. at 201631 we had schedules dated on FY 16, 17, 18 and in all quarters of each year.

[ElizaF]

Ok. I don't understand correct your request.

[#8]

Hi Ruben thanks again for your reply.

Technically speaking Schedules and Actual Sales are two different things and one cannot be found by involving the other.

I gave a good explanation of how in theory the sales and schedules are related in order to provide a brief description of my scenario. However my problem is technical once.

Basically all I want to achieve is the sum of actual sales that was perform on a particular year and quarter prior to the schedule capture 'Week'.