Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Problem with average calculation

Hi

I have this situation:

SALES

Key

Key

subsidiary+article

SubsidiaryDateMovClientArticleAmount
12009999milanabcmilan10/10/20121234abc120
romedfgrome10/08/20129876dfg130
12007777naplejklnaple10/06/20124567jkl200
genoawergenoa10/04/20128901wer350

COST

KeySubsidiaryDatePurchArticleCost
12009999milan10/05/2012abc100
12007777naple10/03/2012jkl150
12005555genoa10/03/2012wer250
12008980rome10/09/2012dfg95
12003875rome09/10/2012dfg100
12002698naple09/29/2012jkl120
12006945milan10/01/2012abc105
12001856genoa09/15/2012wer200

COST_NO_KEY

Key

subsidiary+article

Subsidiary_nkDatePurch_nkArticle_nkCost_nk
milanabcmilan10/01/2012abc105
milanabcmilan10/05/2012abc100
romedfgrome09/10/2012dfg100
romedfgrome10/09/2012dfg95
naplejklnaple09/29/2012jkl120
naplejklnaple10/03/2012jkl150
genoawergenoa09/15/2012wer200
genoawergenoa10/03/2012wer250

I have to calculate marginality for every single sales.

The problem is that not all sales are linked to cost, so i have created a second cost table with a created key that connect the single sales to every purchase done by subsidiary for a specific article.

I need to calculate an average cost of the last 10 days before sales. And in case in the last 10 days there's no purchase calculate an average cost of the last three purchases available before sale.

I use this function to calculate the 10 day average cost

avg(if(datepurch_nk>=date(datemov-10) and datepurch_nk<=datemov,cost_nk))

i have a function that calculate the first purchase before sales but i can't use this in other aggragation

max(if(datemov>=datepurch_nk,datepurch_nk))

COST_NO_KEY is ordered by subsidiary, article and date.

Can someone help me please?

Thanks