Am i using correct logic for Year to date calender year

[harleen_singh]

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

[SunilChauhan]

hope this help

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

[harleen_singh]

Sunil,

thanks but its not working returning 0 only.

thanks

Lavi

[Miguel_Angel_Baeyens · Accepted solution]

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

[SunilChauhan]

make year in script

year(OrderDate) as Year

and now try this

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