Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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?
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.
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?
Just a first idea:
MinMax:
LOAD
Min(dat) as MinDate,
Max(dt) as MaxDate
Shouldn't you use dat field in both aggregations?