topic Re: date function in QlikView

date function

ilanbaruch — Wed, 23 Oct 2013 10:31:26 GMT

how can i compare between this year to last year sales in a pivot?

saumyashah90 — Wed, 23 Oct 2013 10:37:41 GMT

D1=Date(DateField,'DD-MM-YYYY')

vCurYearDate1=Date(DateField,'DD-MM-YYYY');

vPrevYearDate=Date(AddYears(DateField,-1),'DD-MM-YYYY');

You can us this two variables

in your expression such as

sum({$<Field=$(vCurYearDate1)>} Sales);

sum({$<Field=$(vPrevYearDate)>} Sales);

vikasmahajan — Wed, 23 Oct 2013 10:38:27 GMT

Hii,

Following expressions

For Curr Year

=sum($<FiscalYear={$(=max(FiscalYear))}>} Sales)

for Last Year

=sum($<FiscalYear={$(=max(FiscalYear)-1)}>} Sales)

Vikas

jvitantonio — Wed, 23 Oct 2013 10:39:22 GMT

You can either add the field Year as a dimension or, if you don't want to add Year as a dimension, you can do:

Dimension tab: client (example)

Expression:

1) First expressions, called "Sales TY": sum({$<Year = $(=year(today())) >} Sales)

2) Second Expressions, called "Sales LY": sum({$<Year = $(=year(today())-1) >} Sales)

This will give you the Sales for this year and last year for the dimension Client.

Make sure you have in your model a field Year.

Hope this helps.

ilanbaruch — Wed, 23 Oct 2013 10:46:14 GMT

it helped,

thank you

Wed, 23 Oct 2013 10:54:19 GMT

Select year as a dimension

=sum({<Date={$(vmaxyear)},FinancialYear=,FinMonthName=>}Scores)

vmaxyear=max(year(Date))

=sum({<Date={$(vmaxyear-1)},FinancialYear=,FinMonthName=>}Scores)