Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
priyarane
Specialist
Specialist

Date Issue

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;


9 Replies
hic
Former Employee
Former Employee

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

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
priyarane
Specialist
Specialist
Author

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,

priyarane
Specialist
Specialist
Author

Hi HTH,

Its a dimension.

Not applicable

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

priyarane
Specialist
Specialist
Author

Hi Krishnama,

Same issue exists with your script.

PrashantSangle

Hi,

Simply use

MonthName(TempDate) as MonthYear

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
priyarane
Specialist
Specialist
Author

Hi Max,

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

PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂