I created the following variable
First_Day_3_months_ago
_first_of_month(_add_months (current_date;-3)) which will yield the following Jan 1, 2016
I want to create and display a label that concatenates the Month and Year
(ie Jan 2016)
I tried creating a variable called Month3 to display Jan. However it displays 1
How can I display the Jan 2016 in a label and how should the aggregate and rollup be handled
Quote from: que1983 on 04 Apr 2016 10:37:53 AM
I created the following variable
First_Day_3_months_ago
_first_of_month(_add_months (current_date;-3)) which will yield the following Jan 1, 2016
I want to create and display a label that concatenates the Month and Year
(ie Jan 2016)
I tried creating a variable called Month3 to display Jan. However it displays 1
How can I display the Jan 2016 in a label and how should the aggregate and rollup be handled
You didn't tell us what you did to create your Month3 calculation... I'm guessing you used an extract() function?
Depending where you want to use this, you may not need to do any further calculations other than First_Day_3_months_ago. You could simply use the date formatting options on this item to show the short month name and the year? Would that work?
MF.
I thought I did tell you. I created a variable and in the expression I used _first_of_month(_add_months (current_date;-3)) This is an expression function that lists the first day of the month 3 months ago. I did use the date format feature you recommend and I get a strange data type varchar error that I do not understand.
Try below code to get outpout like JAN-2016
if(extract(month, _first_of_month(_add_months (current_date;-3)) )= 1) then
('JAN -' +cast(extract(year,_first_of_month(_add_months (current_date;-3))),varchar(4)) )
else
if (extract(month, _first_of_month(_add_months (current_date;-3)) )= 2) then
('FEB -'+cast(extract(year,_first_of_month(_add_months (current_date;-3))),varchar(4)) )
else
(Null)
Quote from: que1983 on 04 Apr 2016 01:36:27 PM
I thought I did tell you. I created a variable and in the expression I used _first_of_month(_add_months (current_date;-3)) This is an expression function that lists the first day of the month 3 months ago. I did use the date format feature you recommend and I get a strange data type varchar error that I do not understand.
Hi,
You originally told us that's the expression for the "First_Day_3_months_ago" calculation. You then said:
"I tried creating a variable called Month3 to display Jan. However it displays 1". Unless I'm missing something, you didn't tell us how this second calculation (Month3) was defined? I assumed you used an extract() function in this second calculation? Or did you do something different?
What was the error you got when using date formatting? Did you apply the format to the first calculation?
MF.