Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Sara
Partner - Contributor II
Partner - Contributor II

Variance Analysis

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

 Immagine.png

 

img2.PNG

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

Labels (1)
9 Replies
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
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor II
Partner - Contributor II
Author

I  try this script:

img3.PNG

This is the result I expected:

img4.PNG

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
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor II
Partner - Contributor II
Author

Thank you! 

sunny_talwar

No problem 🙂