topic Re: filtered field not works in QlikView

filtered field not works

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: filtered field not works

sunny_talwar — Mon, 19 Dec 2016 19:32:22 GMT

Look here for Dates in Set Analysis

Re: filtered field not works

Mon, 19 Dec 2016 20:09:38 GMT

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.

Re: filtered field not works

vinieme12 — Mon, 19 Dec 2016 21:53:14 GMT

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)

Re: filtered field not works

sunny_talwar — Tue, 20 Dec 2016 02:49:59 GMT

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)

Re: filtered field not works

Tue, 20 Dec 2016 11:47:18 GMT

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

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

Re: filtered field not works

vinieme12 — Wed, 21 Dec 2016 09:34:32 GMT

Try with

Variable Definition

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

Expression

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

Re: filtered field not works

Thu, 22 Dec 2016 20:51:28 GMT

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)

Re: filtered field not works

Thu, 22 Dec 2016 21:00:10 GMT

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.

Re: filtered field not works

Mon, 09 Jan 2017 15:29:28 GMT

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.