topic Re: How I get the formula YearToEnd in QlikView

How I get the formula YearToEnd

Wed, 03 Sep 2014 15:10:41 GMT

Hello together,

I Need your help for a formula

Now is June and I Need the formula from 7-12 dynamiclly. I transform the YTD to a YTE Formual. But it Shows the wrong values.

My YTE formula is

Sum({$<Nature={'Estimate 0'}, [Fiscal Year]=, month=, DateNum={">=$(=Max(DateNum)+1)<=$(=Num(YearEnd(Max(DateNum)))))"}>}Amount)

Do you have a solution?

Thanks

Re: How I get the formula YearToEnd

MK_QSL — Wed, 03 Sep 2014 15:13:24 GMT

=SUM({<

Nature={'Estimate 0'},

[Fiscal Year]=,

month=,

DateNum= {'>=$(=NUM(Today()))<=$(=NUM(YearEnd(Today())))'}>}Amount)

Re: How I get the formula YearToEnd

Thu, 04 Sep 2014 06:33:34 GMT

Thank you for this formula

But for the first NUM(Today()) i Need the current Month +1 (e.g. Juni +1 = Juli), it should be dynamically which month is selected.

(Num(YearEnd(today())) here I Need the dynamically the year which is selected (e.g. the current year 2014, it should Show to the End of the year 2014.

Do you have a solution for me.

Thanks

Re: How I get the formula YearToEnd

tresB — Thu, 04 Sep 2014 06:43:43 GMT

May be like:

NUM(AddMonths(Today(),1)) // to get next month

Re: How I get the formula YearToEnd

jagan — Thu, 04 Sep 2014 06:49:02 GMT

Hi,

Try like this

Sum({$<Nature={'Estimate 0'}, [Fiscal Year]=, month=, DateNum={">=$(=MonthStart(Today(), 1))<=$(=Num(YearEnd(Max(DateNum))))"}>}Amount)

Regards,

jagan.

Re: How I get the formula YearToEnd

Thu, 04 Sep 2014 06:54:55 GMT

Hello,

is the formula Today() and the selected month the same like May(DateNum)?

Re: How I get the formula YearToEnd

jagan — Thu, 04 Sep 2014 07:00:55 GMT

Hi,

Missed Num() to convert date to number.

Sum({$<Nature={'Estimate 0'}, [Fiscal Year]=, month=, DateNum={">=$(=Num(MonthStart(Today(), 1)))<=$(=Num(YearEnd(Max(DateNum))))"}>}Amount)

Regards,

Jagan.

Re: How I get the formula YearToEnd

jagan — Thu, 04 Sep 2014 07:02:37 GMT

Hi,

Today() will give current date (Today - 4th September)

Max(DateNum) - will give the max date in the date field for the current selections.

Regards,

Jagan.