Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm new to the qlikview environment 🙂 I would like to find out if it is possible to have a custom reporting period in Qlikview as a selection? Meaning that if the reporting period for the customer was the 18th of every month to the 17th of the next month, how would we be able to show it as a single selection. Ex. 18 January 2012 to 17 February 2012 would show as Reporting month February.
Thanks in advance,
Terence
Hi
I suggest that you incorporate the reporting period number into your master calendar. Then you can add a list box for reporting period or use reporting period as a dimension.
In your case, add something like one of the following lines to the load of the master calendar.
Month(Date) + If(Day(Date) > 17, 1, 0) As ReportingPeriod,
or
Year(Date) & Num(Month(Date) + If(Day(Date) > 17, 1, 0), '00') As ReportingYM,
If you are not using a master calendar, there are many postings on that topic. Search for "master calendar" for more information. A minimal master calendar could be:
LOAD Date(Date) As Date,
Month(Date) As Month,
Year(Date) As Year,
Month(Date) + If(Day(Date) > 17, 1, 0) As ReportingPeriod
;
LOAD $(vMinDate) + RecNo() - 1 As Date
AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;
Where vMinDate and vMaxDate are the date range to use (in number format). Rename the field Date to refer to the transaction or fact date in your model.
Regards
Jonathan
Corrected last LOAD with RecNo() in place or RowNo() and value of 18 with 17
Hi
I suggest that you incorporate the reporting period number into your master calendar. Then you can add a list box for reporting period or use reporting period as a dimension.
In your case, add something like one of the following lines to the load of the master calendar.
Month(Date) + If(Day(Date) > 17, 1, 0) As ReportingPeriod,
or
Year(Date) & Num(Month(Date) + If(Day(Date) > 17, 1, 0), '00') As ReportingYM,
If you are not using a master calendar, there are many postings on that topic. Search for "master calendar" for more information. A minimal master calendar could be:
LOAD Date(Date) As Date,
Month(Date) As Month,
Year(Date) As Year,
Month(Date) + If(Day(Date) > 17, 1, 0) As ReportingPeriod
;
LOAD $(vMinDate) + RecNo() - 1 As Date
AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;
Where vMinDate and vMaxDate are the date range to use (in number format). Rename the field Date to refer to the transaction or fact date in your model.
Regards
Jonathan
Corrected last LOAD with RecNo() in place or RowNo() and value of 18 with 17
Example attached
Hi Jonathan 🙂
Thank you very much.I'm busy adding it in 🙂
Regards
Terence