Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I have one "Full_Date" column as
20000101
20000102
20000103......like this i have till 20251231. It is not of type date. it is just stored as string in database with varchar(8).
I made a list box for month and date using the Full_Date column. Below the way i followed.
in General tab of list box, in the field dropdown, i have selected "<Expression>" and entered the expression as below.
=if(left(right(Full_Date,4),2)=1, 'Jan',
if(left(right(Full_Date,4),2)=2, 'Feb'.....like this i have created all 12 months
=if(right(Full_Date,2)=1,1,
if(right(Full_Date,2)=2, 2....like this i created numbers 31 for date field.
so, i have month field, date field.
Question:
I need to create a pivot table here. Columns are
Dimension is Region
Expressions: Current Month sales, Current Month Target, Previous month sales, Previous month target..
Here, whatever month i have selected from the month field is my current month.
How to keep a month using set analysis in current month sales expression, previous month sales expression.
I have created month and date fields but those are just expressions, not available in the fields list.
How to make this month expression as month field and date expression as date field? Please suggest.
Note: Date conversion function (Date#) didn't workout for this scenario.
I think date#() will work, see this for your first date-value:
=date(date#(20000101, 'YYYYMMDD'), 'DD.MM.YYYY')
and then you should use a master-calendar to link these date to period-fields like month and year. Maybe these approach needs this or that adjustment in relation to your data but in each case it's better than your heavly nested if-loop approach.
Here you could find more about: How to use - Master-Calendar and Date-Values
- Marcus
You should really look into parsing your field values as dates, then create a master calendar, or at least create calendar fields from your date. If you have create fields for your calendar, then you can easily apply set analysis (which is operating on field values):
LOAD *,
Date(Full_Date) as Date,
Month(Full_Date) as Month,
Year(Full_Date) as Year;
LOAD
Date#(Full_Date, 'YYYYMMDD') as Full_Date,
...
FROM YourTable;
See also
Hi,
check this,
Data:
LOAD *,
Date(Date#(SysDate,'YYYYMMDD'),'DD/MM/YYYY') as Date,
//Mid(SysDate,7,8)&'/'&left(Mid(SysDate,5,6),2)&'/'&Mid(SysDate,1,4) as Date,
Month(Date(Date#(SysDate,'YYYYMMDD'),'DD/MM/YYYY')) as MonthName,
Num(Month(Date(Date#(SysDate,'YYYYMMDD'),'DD/MM/YYYY'))) as MonthNum
INLINE [
Region, SysDate, Sales
State 1, 20150130, 88
State 1, 20150130, 585
State 2, 20150228, 5245
State 2, 20150228, 54
State 3, 20150228, 577
State 3, 20150328, 54
State 1, 20150328, 77
State 1, 20150328, 888
State 2, 20150228, 99
State 1, 20150328, 66
State 2, 20150328, 77
State 3, 20150130, 78
State 1, 20150130, 52
State 1, 20150228, 366
State 1, 20150130, 525
State 1, 20150130, 74
State 1, 20150228, 411
State 1, 20150130, 44
];
By using this fields ,at front end:
Current Month sales Expression: =Sum({<MonthNum={'$(=Max(MonthNum))'}>}Sales)
Previous Month sales Expression: =Sum({<MonthNum={'$(=Max(MonthNum)-1)'}>}Sales)
Current Month sales According to data of today Expression: =
SUM({<DateField ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Sales)
Previous Month sales According to data of today Expression: =
SUM({<DateField ={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Amount)
Hope this helps,
Regards,
PFA,
Hirish
There is a problem with your expression. They always show you the maximum month and the previous. Maximum month could not be the current month.
To select current month you have to use the function Today(). for example: Num(Month(Today()))
May be you could use a variable like: vCurrentMonth = Num(Month(Today()))
and another variable to the previous month: vPreviousMonth = $(vCurrentMonth)-1
Hi Srinivas,
First format the date in script like below then it would be easier to perform this type of operations.
Data:
LOAD
*,
Month(Full_Date_Format) AS Month,
Year(Full_Date_Format) AS Year;
LOAD
*,
Date(Date#(Full_Date, 'YYYYMMDD')) AS Full_Date_Format
FROM DataSource;
Now in front end use below expressions
Current Month Sales: Sum({<Year=, Month=, Full_Date_Format={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>} Sales)
Previous Month Sales:Sum({<Year=, Month=, Full_Date_Format={'>=$(=MonthStart(Today(), -1))<=$(=MonthEnd(Today(), -1))'}>} Sales)
Current Month Target: Sum({<Year=, Month=, Full_Date_Format={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>} Target)
Previous Month Target:Sum({<Year=, Month=, Full_Date_Format={'>=$(=MonthStart(Today(), -1))<=$(=MonthEnd(Today(), -1))'}>} Target)
Hope this helps you.
Regards,
Jagan.
For all my earlier reports, we used the date format which is mentioned in the environment variables of the script window.
Is there any possibility to convert my date string into the date format which is same to Date format in environmental variables.
Date(Date#(Full_Date,'YYYYMMDD'),'MM/DD/YYYY')
Is my above Code rite? will it work?
This is my script
LOAD Calendar_Month,
Calendar_Year,
Date_Key,
Full_date,
Date(Date#(Full_date,'YYYYMMDD'),'MM/DD/YYYY') as FullDate,
Month(FullDate) as Month,
Date(FullDate) as Date,
Year(FullDate) as Year,
Qtr
from
time.qvd;
when executing the script, it is saying FullDate field not found. Please suggest
Hi,
Yeah i thought according to data he need the output.Thanks for update.
-Hirish
I think you have here a typo on FullDate then in your script are three different versions of them which seems unlikely.
- Marcus