topic Am i using correct logic for Year to date calender year in QlikView

Am i using correct logic for Year to date calender year

harleen_singh — Fri, 05 Aug 2011 11:10:15 GMT

Hello,

i want to calculate sales for Year to date. that is a calender year not fiscal

So that means from 1st january current year to today

I am using following logic. is it correct? if it is not then why?

if(OrderDate<=Today() and OrderDate>=YearStart(Today()),sum(Netamountpaid))

Regards

Lavi

Am i using correct logic for Year to date calender year

SunilChauhan — Fri, 05 Aug 2011 11:18:18 GMT

hope this help

sum(if( OrderDate>=YearStart(Today()) and OrderDate<=Today() ,Netamountpaid))

Am i using correct logic for Year to date calender year

harleen_singh — Fri, 05 Aug 2011 11:27:06 GMT

Sunil,

thanks but its not working returning 0 only.

thanks

Lavi

Re: Am i using correct logic for Year to date calender year

Miguel_Angel_Baeyens — Fri, 05 Aug 2011 11:40:26 GMT

Lavi,

It should be rather

Sum(If(OrderDate <= Today() AND OrderDate >= YearStart(Today()), Netamountpaid))

So the If() fits within the aggregation function Sum(). However, this will perform really poor with a few hundreds of records, so you better create a flag field in the script to know wheter a date fits or doesn't fit in the year to date calculation.

Table:
LOAD *,
     InYearToDate(DateField, Date('28/02/2011'), 0, 3) AS FlagYTD
FROM ...

The expression above will return true ("-1") if the value in field "DateField" is between "01/03/2011" (that's because the 3 as the fourth parameter, that indicates the fiscal year starts on August) and "28/02/2012".

Once this is built, the expression to sum between both dates would look like

Sum({< FlagYTD = {'-1'} >} Netamountpaid)

Now using variables you can compare to year to date periods of previous years and so.

Note that in QlikView both field names and values are case sensitive. Not as well that in your expression OrderDate must have the same exact format than the returned by "Today()" or "YearStart(Today())". Another advantage of flag fields is that you don't have to care about this.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Am i using correct logic for Year to date calender year

SunilChauhan — Fri, 05 Aug 2011 11:55:11 GMT

make year in script

year(OrderDate) as Year

and now try this

sum(if( Year>=Year(Today()) and OrderDate<=Today() ,Netamountpaid))