topic Re: value 2016 until today in QlikView

value 2016 until today

master_student — Fri, 20 Oct 2017 11:36:47 GMT

Hello Guys,

How to change this expression bellow so it gives me the value of year -1 until today 20/10

My calendar dimension contains : Calendar Date with that format : 20/10/2017

num(sum( {$<[Year of date]={$(=Max([Year of date])-1)}>} recharge_amount),'# ##0'))

Thanks in advance

Re: value 2016 until today

sunny_talwar — Fri, 20 Oct 2017 11:38:44 GMT

Is Year of Date a year field or date field?

Re: value 2016 until today

master_student — Fri, 20 Oct 2017 11:39:13 GMT

Year field

Re: value 2016 until today

Anil_Babu_Samineni — Fri, 20 Oct 2017 11:39:46 GMT

This?

num(sum( {$<[Year of date]={">= $(=Max([Year of date])-1) <=$(=Today())"}>} recharge_amount),'# ##0'))

Re: value 2016 until today

sunny_talwar — Fri, 20 Oct 2017 11:40:48 GMT

May be this

num(sum( {$<[Year of date]={$(='>=' & (Max([Year of date])-1))}>} recharge_amount),'# ##0'))

Re: value 2016 until today

sunny_talwar — Fri, 20 Oct 2017 11:41:28 GMT

I am assuming that you won't have future data in your app... if you do, then you will need to use date instead of year field

Re: value 2016 until today

master_student — Fri, 20 Oct 2017 11:49:15 GMT

Hello Sunny,

Thanks

whene I use your expression i get this error:

Error: Error in set modifier ad hoc element list:

',' or ')' expected

the year field like this

Re: value 2016 until today

master_student — Fri, 20 Oct 2017 11:50:00 GMT

your expression give me the total of 2016 and 2017 until today, I need the total of 2016 until 20/10/2016

Re: value 2016 until today

sunny_talwar — Fri, 20 Oct 2017 11:57:12 GMT

Try this

num(sum( {$<[Year of date]={"$(='>=' & (Max([Year of date])-1))"}>} recharge_amount),'# ##0'))

Re: value 2016 until today

master_student — Fri, 20 Oct 2017 12:05:08 GMT

Thanks Sunny, same thing as Anil, your expression give me the total of 2016 and 2017 until today, I need the total of 2016 until 20/10/2016

Re: value 2016 until today

sunny_talwar — Fri, 20 Oct 2017 12:07:56 GMT

Do you have a date field in your application?

Re: value 2016 until today

Anil_Babu_Samineni — Fri, 20 Oct 2017 12:13:52 GMT

What are you getting when you use this? Year(AddYears(Date(Today()),-1))

Re: value 2016 until today

mostwanted123 — Fri, 20 Oct 2017 12:14:22 GMT

Try this,

num(sum( {$<[Year of date]={">= $(=Yearstart(Addyears(Max([Year of date])),-1)) <=$(=Addyears(Today()),-1)"}>} recharge_amount),'# ##0'))

Regards,

Pratik Bhor

Re: value 2016 until today

sunny_talwar — Fri, 20 Oct 2017 12:15:36 GMT

But Anil how can you compare date to an year field brother?

Re: value 2016 until today

Anil_Babu_Samineni — Fri, 20 Oct 2017 12:18:38 GMT

We can't with out Date field Bro

Re: value 2016 until today

sunny_talwar — Fri, 20 Oct 2017 12:19:27 GMT

and that is exactly what I asked above

Re: value 2016 until today

mostwanted123 — Fri, 20 Oct 2017 12:20:39 GMT

Sorry for the extra bracket,

num(sum( {$<[Year of date]={">= $(=Yearstart(Addyears(Max([Year of date])),-1)) <=$(=Addyears(Today()),-1)"}>} recharge_amount),'# ##0')

Regards,

Pratik Bhor

Re: value 2016 until today

master_student — Fri, 20 Oct 2017 12:21:03 GMT

yes it's Calendar date

Re: value 2016 until today

sunny_talwar — Fri, 20 Oct 2017 12:23:10 GMT

What is the format of this field? May be try this

Num(Sum({$<[calendar date] = {"$(='>=' & Date(AddYears(Today(), -1), 'DateFieldFormatHere') & '<=' & Date(AddYears(Today(), 0), 'DateFieldFormatHere'))}>} recharge_amount),'# ##0'))

Re: value 2016 until today

master_student — Fri, 20 Oct 2017 12:28:33 GMT

the format is DD/MM/YYYY