Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Djaber
New Contributor

Help! Date filter (Realease november 2018 "Date picker")

Hi everyone, 

I'm beginner in Qlik sense and I have a question for the Calendar Object added in the last release of november 2018, i have one KPI which show me the turnover of my business in the first measure and the difference with the last year in the seconde measure. it worked very good When i used the year filter, but since i'm using the calendar filter the second measure wasn't calculated( when a select from 01/01/2015 until 31/12/2015). in the pictures bellow you wil understood the problem beter.

All the graphics in the picture below are juste for demonstartion and to show you that data's exists. WHEN I CHANGE THE FILTER IN CALENDAR FILTER I GET THE VALUE '0' INSTEAD OF the logical VALUE.

 the formula here is simple and clear: 

sum ( {< YEAR = {$(=Year(Max(DATE))-1)} >} SOLD_QTY * [SALE PRICE] ) 

did it happen for you or could one of you tell me that it's may be a bug .

Thanks in advance 

Djaber T

Qli-Calendar.png

 

 

 

4 Replies
jsaintmaxent
Contributor II

Re: Help! Date filter (Realease november 2018 "Date picker")

hi,

it's possible to have the complete expression form the second measure?

bye

Djaber
New Contributor

Re: Help! Date filter (Realease november 2018 "Date picker")

Hi @jsaintmaxent

the expression of the seconde meaasur is a simple ratio:

sum ( {< YEAR = {$(=Year(Max(DATE))-1)} >} SOLD_QTY * [SALE PRICE] )  /  sum ( {< YEAR = {$(=Year(Max(DATE)))} >} SOLD_QTY * [SALE PRICE] ) 

Thanks in advance.

Djaber

 

jsaintmaxent
Contributor II

Re: Help! Date filter (Realease november 2018 "Date picker")

hi,

i think your problem is :

your set analysis is on the field year so when you use year expression works find

if you select 01/01/2015 for example and you put in your set analysis year=2015, the expression works

if you select 01/01/2015 for example and you put in your set analysis year=2014, the expression doesn't work because Qlik keeps the field date so it understands sum of my measure for the date 01/01/2015 and year =2014, it's not logic for it

dis you try this :

sum({$<DATE={">=$(=makedate(year(min(DATE)-1),mid(min(DATE),4,2),left(min(DATE),2)))<=$(=makedate(year(min(DATE)-1),mid(min(DATE),4,2),left(min(DATE),2)))>} SOLD_QTY * [SALE PRICE])

or

sum({$<DATE={">=$(=date(left(min(DATE),6))&(year(min(DATE)-1),'DD/MM/YYYY)})<=$(=date(left(max(DATE),6))&(year(max(DATE)-1),'DD/MM/YYYY})"}> SOLD_QTY * [SALE PRICE])

Bye

 

jsaintmaxent
Contributor II

Re: Help! Date filter (Realease november 2018 "Date picker")

erratum:

sum({$<DATE={">=$(=makedate(year(min(DATE)-1),mid(min(DATE),4,2),left(min(DATE),2)))<=$(=makedate(year(max(DATE)-1),max(min(DATE),4,2),left(max(DATE),2)))>} SOLD_QTY * [SALE PRICE])