Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Weighted avg in FirstSortedValue

!I am trying to compare actual purchased amount over selected period of time compared to base period amount. Where base period amount is the price going into the selected period applied to the quantity during the period.

What is the actual result?

I got the firstsortedvalue to work but fails when we have 2 or more different prices on the same date for the same item and facility. This is due to multiple suppliers delivering the same item.

What I want?

I want to get a weighted avg price for those multiple suppliers for that date and item. This avg price I can then use to multiply on the Quantity for the selected period.

In the case where there are 2 or more prices I need the inner function to calculate the weighted price.

Firstsortedvalue(Distinct

Price1*Quantity1+Price2*Quantity2

/

(Quantity1+Quantity2)

In my example you will see 3 formulas,

Purchase value EUR = Just the spend for the period

Purchased value base period EUR = Quantity for the period * Price closest before Startdate (or closest after Startdate if there is no purchasee 12 months before startdate)

Savings EUR = Purchase value EUR - Purchased value base period EUR

0 Replies