topic Re: Filtering with the date fields goes wrong (wrong values) in QlikView

Filtering with the date fields goes wrong (wrong values)

OmarBenSalem — Wed, 27 Jan 2016 19:15:35 GMT

Hi all, I have a dimension :" région" and another one called "RATE" which only can be either R-Bonne or R-Mauvaise.

I have made a table in which the dimension was "region" and as expressions, here is what i did:

1)Count({<RATE=’R-Bonne’>} RATE) : to count the number of good rates (R-Bonne)

2) Count({<RATE=’R-Mauvaise’>} RATE) : to count the number of bad rates (R-Mauvaise)

It all went good, the values were correct.

If we filter with other dimensions, for example with cities, we always obtain an accurate result.

BUT, when I try to focus on a specific date (year, month or quarter), the result goes wrong, so wrong !

For example, in Tunis, in 2015 I should have about 37000 good rates, I'm only having 147 !

I tried to change the expression as so:

Count({<Year=, Month=, Quarter=, RATE=’R-Bonne’>} RATE)

But, when doing this, nothing change when I filter with date, the value doesn't change. It's always 87 000 weither I'm in 2015, 2014 or in the 2 july 2014 ! as I've never filtered with the date !

ps: with my other analysis, I don't have a problem when filtering with the date. I only have one date field; I've created my master calendar with it, and I've already filtered using the date with other analysis and had accurate results..

Another thing, each "num" has a related "RATE' (R-bonne / R-mauvaise), should I do a record count of the num?

Num as NumRecordCounter ? And then change the expression as follow:

1)Count({<RATE=’R-Bonne’>} NumRecordCounter)

2) Count({<RATE=’R-Mauvaise’>} NumRecordCounter)

Hope you'll help ! 'm really struggling, Thanks !

Re: Filtering with the date fields goes wrong (wrong values)

swuehl — Wed, 27 Jan 2016 19:53:16 GMT

That's hard to answer without knowing your data model.

How are the relevant fields related to each other? Do you have date values for each record?

Could you post a small sample QVW?

Re: Filtering with the date fields goes wrong (wrong values)

OmarBenSalem — Wed, 27 Jan 2016 20:11:39 GMT

I only have one table and one date field with which I've created my master calendar.

I would have loved to post something, but I don't have the qv doc in my personal computer now.

Re: Filtering with the date fields goes wrong (wrong values)

OmarBenSalem — Thu, 28 Jan 2016 10:32:55 GMT

the field date I'm using is "dat", when I create a list box with the expression "year(dat)" as a field and filter with it, the values are good.

But when I use the year I've created in my master calendar, the values are completely wrong:

here is my master calendar:

//******************* MinMax table**************
//
MinMax:
LOAD
Min(dat) as MinDate,
Max(dt) as MaxDate

Resident test;
//
//
//
//******* defining Min and Max dates**********
//
LET vMinDate= Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate= Num(Peek('MaxDate',0,'MinMax'));
LET vToday=$(vMaxDate);

//
//************ creating the temp calendar******
//
TempCal:
LOAD
Date($(vMinDate)+ RowNo()-1) as TempDate
AutoGenerate
$(vMaxDate)-$(vMinDate)+1;

DROP Table MinMax;

//
//************ the MasterCalendar table ******
MasterCalendar:
LOAD
TempDate as dat,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate)as WeekDay,
'Q'&Ceil(Month(TempDate)/3) as Quarter,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
Week(TempDate)&'-'&Year(TempDate) as WeekYear,
InYearToDate(TempDate, $(vToday),0)* -1 as CurYTDFlag,
InYearToDate(TempDate, $(vToday),-1)* -1 as LastYTDFlag
Resident TempCal
Order By TempDate ASC;

DROP Table TempCal;

--------------------------

the dat field in my test table is well formated. How come I'm having such a trouble?

Re: Filtering with the date fields goes wrong (wrong values)

swuehl — Thu, 28 Jan 2016 10:50:58 GMT

Just a first idea:

MinMax:
LOAD
Min(dat) as MinDate,
Max(dt) as MaxDate

Shouldn't you use dat field in both aggregations?