topic Re: Problem with se analysis in QlikView

Problem with se analysis

Mon, 22 Oct 2012 12:16:53 GMT

Good morning to everyone

I have two tables one with sales and one with goods cost.

Every sale is connected with every goods purchase of the year using article code.

For every sale i need to calculate an average cost calculated from date of sell -10 days to date of sell (if there aren't purchase in this period i have to consider the first purchase before the date of sell -10).

datamov-->date of sell
datacos-->date of purchase.

I use this expression:
Avg({$<datacos={">=$(=date(datamov-10)) <=$(=date(datamov))"}>}goods_costs)

but costs are calculated on the entire year.

Thanks in advance for help.

Re: Problem with se analysis

Gysbert_Wassenaar — Mon, 22 Oct 2012 12:23:41 GMT

Make sure datacos and datamov are real dates and not strings. Use the date# function if necessary (preferably in the load script). If your dateformat is 'DD/MM/YYYY' you can try:

avg({$<datacos={">=$(=date#(datamov),'DD/MM/YYYY')-10)<=$(=date#(datamov),'DD/MM/YYYY')"}>}goods_costs)

Re: Problem with se analysis

Mon, 22 Oct 2012 13:57:37 GMT

Thanks Gysbert but datamov and datacos are both real dates.

Now expression work but only if a datamov is selected otherwise value is null.

Avg({$<datacos={">=$(=date(datamov-10)) <=$(=date(datamov))"}>} goods_costs)