filtered field not works

[#1]

Hello

This is my first post, and really I searched a lot before asking this.

I have two pivot table charts with same columns.

The first table use the actual selection.

The second table is my problem: I need the data of old year( as 'retail week'). For example, today is 19.December.2016 , I don't need 19.December.2015, I need 21.December.2015 .

For this, I use date-364. In text object it works, but in the chart it doesnt work.

My expression is

count(distinct MOVNUM & EMPRESA)

My possible fields for dates are:

- movfec: date of row, this is the real date.

- diaYearAnterior: of  date type -number format. movfec-364

- Año = year(movfec)

- Mes = month(movfec)

- Dia = day(movfec)

I tried with variables, but not works. My chart is 0 rows.

I tried with expressions, but not works.

Some examples i used:

count({<MOVFEC={$(=diaYearAnterior}>}distinct MOVNUM & EMPRESA)

count({<MOVFEC=$(MakeDate(Año,Mes,Dia)-364)>}distinct MOVNUM & EMPRESA)

count({<MOVFEC={$(vYearMinus1)}>} distinct MOVNUM & EMPRESA)   <--- variable with =MOVFEC-364

I don't have a formal study of Qlik, please guide me.

[sunny_talwar]

Look here for Dates in Set Analysis

[#3]

you cannot search for 42005 when you want Jan 1st 2015, even though this is the value of the date.​

ok. I changed my variable for

=Date(MOVFEC-364)

but not works.

In the blog, we have uses to Addmonths for work with dates. Well, I need for days and AddDays() doesnt exists.

[vinieme12]

what date format is MOVFEC? dd/mm/yyyy ?? you need to give the same format in your set expression

count({<MOVFEC={'$(=DATE(vYearMinus1,'DD/MM/YYYY'))'}>} distinct MOVNUM & EMPRESA)

[sunny_talwar]

May be the date formats are different as well.... vinieme12‌ mentioned the use of date format, I would suggest to check in a text box object if Date() alone gives the same format as MOVFEC or not. If doesn't, then use the second parameter of the date format to provide it with the proper format.

=Date(MOVFEC-364, 'DD/MM/YYYY') or =Date(MOVFEC-364, 'MM/DD/YYYY)

[#6]

MOVFEC is a int. Date(MOVFEC) return correct date.

I will try with your suggestions and i will update status.

[vinieme12]

Try with

Variable Definition

diaYearAnterior:  Interval(Max(TOTAL MOVFEC) - 364,'d')

Expression

=count({<MOVFEC={$(=diaYearAnterior)}>}distinct MOVNUM & EMPRESA)

[#8]

Hi vinieme12‌

I think you're a little confuse: diaYearAnterior is a load field from database. this has my search date.

(diaYearAnterior  MOVFEC-364 in load item)

vYearMinus1 is the variable.

well, I tried vYearMinus1

=Interval(Max(TOTAL MOVFEC) - 364,'d')

with expression

=count({<MOVFEC={$(=diaYearAnterior)}>}distinct MOVNUM & EMPRESA)

and not rows returned.

Well, resuming this:

Selected Date: 12-12-2016 (MOVFEC)

Searched Data in Pivot table Chart: 14-12-2015: still no rows.

Base Expression: count(distinct MOVNUM & EMPRESA)

[#9]

my fault.

I tried:

vYearMinus1 is the variable.

I tried vYearMinus1

=Interval(Max(TOTAL MOVFEC) - 364,'d')

with expression

=count({<MOVFEC={$(=vYearMinus1)}>}distinct MOVNUM & EMPRESA)

and not rows returned.

well, still not works.

[#10]

Update: i have the answer!

all your answers are ok.

My problem was I don't have MOVFEC in my filter panel. I filtered another fields, and I assume MOVFEC works.

I used MOVFEC in filters panel, select it and works! -.-

anyway, thanks for your help.