topic Re: Date Issue in QlikView

Date Issue

priyarane — Mon, 16 Mar 2015 10:49:29 GMT

Hi Experts,

I have added below M.Calendar and my problem is I can't find the column(MonthYear) in my pivot table as attached but I can see in list box. and I can see in Table viewer also.

QuartersMap:

MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(Reporting_Date) as minDate,
max(Reporting_Date) as maxDate
Resident TradeExposure;
//EXIT Script

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
//Left Keep(TradeExposure)
Load

TempDate AS Reporting_Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: Date Issue

hic — Mon, 16 Mar 2015 10:54:07 GMT

My guess is that there is no link between dimension fields (e.g. Division) and the Reporting_Date. But it is hard to tell without the app...

HIC

Re: Date Issue

jonathandienst — Mon, 16 Mar 2015 10:56:37 GMT

Your master calendar looks OK to me, so I suspect the problem is the expression in the pivot table. If the expression is "=MonthYear", then it is possible that there is more than one possible value in each row, so it cannot display a value. Try count or concat to verify this and modify the formula (with an aggregation of some type), so it can only return a single value for each row.

HTH

Jonathan

Re: Date Issue

priyarane — Mon, 16 Mar 2015 10:58:14 GMT

Hi HIC,

We have association as attached and not only MonthYear and all fields in M.Cal, I can't find in pivot table.

Only one date availabe in my data as below:

MonthEnd(Date#(Reporting_Date,'YYYYMM')) as Reporting_Date,

Re: Date Issue

priyarane — Mon, 16 Mar 2015 11:02:26 GMT

Hi HTH,

Its a dimension.

Re: Date Issue

Mon, 16 Mar 2015 11:10:12 GMT

Hi,

you will use this script

Date(SubField(TempDate,' ',1),'MMM-YYYY') AS MonthYear, instead of

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

In above expression, you will try to get 1 or 2 or 3 instead of 1

Re: Date Issue

priyarane — Mon, 16 Mar 2015 11:14:50 GMT

Hi Krishnama,

Same issue exists with your script.

Re: Date Issue

PrashantSangle — Mon, 16 Mar 2015 11:27:27 GMT

Hi,

Simply use

MonthName(TempDate) as MonthYear

take this field in listbox check did you get value or not??

Regards

Re: Date Issue

priyarane — Mon, 16 Mar 2015 11:57:20 GMT

Hi Max,

its working in the List box and not in the Pivot table

Re: Date Issue

PrashantSangle — Mon, 16 Mar 2015 12:04:35 GMT

Hi,

Then create Straight Table take

MonthYear as dimesion and use any simple expression in it

check did you get any value against it or not??

also take Year in list box, Month in list box and check when you select any value from MonthYear list box the related value reflected in Month and Year List box..

If possible post your app.

Regards