topic Re: Calculation for 12 months before in QlikView

Calculation for 12 months before

ecabanas — Sat, 14 Mar 2015 08:01:42 GMT

I'm doing this load script

let vminmonth_WAC=date(Monthend(floor('$(vminmonth_WAC)')+1),'YYYY-MM-DD') ;

LOAD

(SUM(QTY*unit_price))/SUM(QTY) AS CONTADOR_WAC,

ITEMID

RESIDENT VENDINVOICETRANS

WHERE

CREATEDDATETIME <='$(vminmonth_WAC)'

Group by ITEMID;

This calculates the average cost for all the dates, but I only want to calculate the last 12 months or 365 days

Anyone could help to me?

Many thank's

Eduard

Sat, 14 Mar 2015 08:32:45 GMT

let vminmonth_WAC=num(date(max(YourDateFld),'MM-DD-YYYY hh:mm:ss'));

let vminmonth_WAC_last=num(addyears(date(max(YourDateFld),'MM-DD-YYYY hh:mm:ss'),-1));

WHERE

CREATEDDATETIME <=$(vminmonth_WAC) and CREATEDDATETIME >=$(vminmonth_WAC_last)

-or-

let vminmonth_WAC_last=num(addyears(date(max(YourDateFld),'MM-DD-YYYY hh:mm:ss'),-1));

WHERE

CREATEDDATETIME >=$(vminmonth_WAC_last)

while doing this crosscheck format of dateField asper your data...

ecabanas — Sat, 14 Mar 2015 09:04:55 GMT

HI,

it returns

error Script:

let vminmonth_WAC_last=num(addyears(date(max(YourDateFld),'MM-DD-YYYY hh:mm:ss'),-1))

you know why???

many many thank's

Eduard

Sat, 14 Mar 2015 09:10:08 GMT

ok try whithin text box for test...

=num(addyears(max(YourDateFld),-1))

It will return exactly previous year last date from your max date & from their onwards till current date data will be taken into consideration...

you want to achieve with same approach or not...