Variance Analysis

[Sara]

Hi, I need to compute the formula below using the data in the table:

 

I want to multiply the quantities of the products of the month of April with the price that the products have in March.

How can I do?

Thanks

[sunny_talwar]

Which object are you trying to do this in? Are you also looking at this two months at a time or would you want to see 6 months (for example) where each month's quantity is multiplied by previous month's price?

[Sara]

I want to create a KPI object.

I'd like to allow the user to select 2 time periods (2 distinct months), one month is the Target Period and the other is the Actual Period. In this formula I use the Quantity of the Actual Period and the Price of the Target Period.

[sunny_talwar]

May be something like this assuming you have three period fields, Period, Actual Period and Target Period

Sum(Aggr(
Only({<Period = p([Actual Period]), [Actual Period], [Target Period]>} Quantity) *
Only({<Period = p([Target Period]), [Actual Period], [Target Period]>} Price)
,  Product))

[Sara]

I  try this script:

This is the result I expected:

but I get= 15000

[sunny_talwar]

I won't really know what you might be doing wrong just by looking at your expression... would you be able to share a sample to show the issue?

[Sara]

Please can you try this example?

[sunny_talwar]

You can give this a shot

Sum(Aggr(
  Sum({$<DATE ={">=$(=v_startActual)<=$(=v_endActual)"}>} QUANTITY)
  *
  Avg({$<DATE = {">=$(=v_startTarget)"}*{"<=$(=v_endTarget)"}>} PRICE)
, PRODUCT))

[Sara]

Thank you! 

[sunny_talwar]

No problem 🙂