always show YTD figures in a field of a table with free selection

[#1]

Hi all

I'm not sure how I can built a YTD field in a table where you can select whatever you want, but this field should always show YTD.

This is the formular to calculate Revenue

=Num(sum({<Fact.Type = {'Sales'}, Calendar.Year = {$(=Year(Today())-0)}>} Fact.Net_Rev_EUR), '#''##0.00')

The dimension I can choose for Start / End of the year are following:

How would you solve this?

thanks for your help,

Oliver

[buzzy996]

just rename that field in script and run..see..

[rajkumarb]

HI Oliver

You Need to add Calendar.Date using Master Calendar

Create this  variable

vToday = NUM(Today());

vMaxSalesDate =DATE(MAX(Calendar.Date))

and try this 

=Num(sum({<Fact.Type = {'Sales'}, Calendar.Year = {'>=$(=Date(YearStart(vToday)))<=$(=Date(vMaxSalesDate))'} >} Fact.Net_Rev_EUR), '#''##0.00')

[#4]

Hi Raj

vToday = 42102

vMaxSalesDate = 31.12.2015

Result = 0

so something is wrong 😞

unfortunately I cannot share the App, do you have any idea?

[PrashantSangle · Accepted solution]

Hi,

Do you have calender.Date field??

then Try with below expression,

sum({<Fact.Type = {'Sales'}, Calendar.Date = {">=$(=YearStart(Today()))<=$(=Today())"}>} Fact.Net_Rev_EUR)

If your Calender.Date is num format after loading in QlikView then use num() in expression like

num(YearStart(Today()))

and

num(Today())

Regards

[#6]

Your formula worked!

thanks for your help

Oliver