Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
if(GetSelectedCount([Month])=0 and GetSelectedCount([Fiscal Year])=0,
num(sum({<calendar_current_month_offset={'$(vDefaultMonthABCInventory)'}, month_end_metric_date_key={'20161031'}>}forecast),'#,##0'), The set analysis should be when the month_end_date_key = month_end_metric_date_key
num(sum({<calendar_year_month = {'$(=Max(calendar_year_month))'}>}forecast),'#,##0')
)
in a text box type '=$(varMaxDate)'
Probably better if you share what you written in script for varMaxDate
In Script
Temp:
Load
Min(month_end_date_key) as minDate,
Max(month_end_date_key) as maxDate
Resident month_end_date_key;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
D_MONTH_END_DATE_TEMP:
NoConcatenate
LOAD *,
dual(Month_Name, ApplyMap('Month_Sort',Month_Name)) as Month;
//dual(Month_Name, ApplyMap('Month_Sort',Month_Name)) as [Month], fiscal_year &'-'& [Month_Name] as [Year Month];
LOAD
month_end_date_key,
month_end_day_date,
makedate(calendar_year,calendar_month_number_in_yr,left(month_end_day_date,2)) as month_end_day_date_converted,
//Date(Date#(month_end_day_date,'DD-MMM-YY'),'M/DD/YYYY') as month_end_day_date_converted,
month_end_day_name,
day_number_in_calendar_year,
day_number_in_fiscal_year,
//Month(subfield(calendar_month_name,3,0)) as MONTH,
//dual(calendar_month_name, ApplyMap('Month_Sort',calendar_month_name)) as Month,
calendar_month_name,
left(calendar_month_name,3) as Month_Name,
calendar_month_number_in_yr,
calendar_year_month,
//Max(calendar_year_month) as MaxMonth,
calendar_qtr,
calendar_qtr_name,
calendar_year_qtr,
calendar_year,
fiscal_month_number_in_yr as [FY Period],
fiscal_year_month,
//FISCAL_YEAR&'-'&MonthName as [Year Month],
fiscal_qtr as [Fiscal Qtr],
fiscal_qtr_name,
fiscal_year_qtr,
fiscal_year as [Fiscal Year],
//[fiscal_year] &'-'& [calendar_month_name] as [Year Month],
month_start_date_key,
month_start_day_date,// as [Date]
makedate(calendar_year,calendar_month_number_in_yr,left(month_start_day_date,2)) as month_start_day_date_converted,
date(today()-1) as YesterdaysDate,
If(makedate(calendar_year,calendar_month_number_in_yr,left(month_end_day_date,2)) >= (date(today()-1)),'TRUE','FALSE') as GreaterThanYesterday,
If(makedate(calendar_year,calendar_month_number_in_yr,left(month_start_day_date,2)) <= (date(today()-1)),'TRUE','FALSE') as LessThanYesterday
FROM
(txt, utf8, embedded labels, delimiter is '|', no eof)
where len(month_end_date_key)=8;
Let vTestValues = 'Not True';
D_MONTH_END_DATE:
NoConcatenate
Load *,
//if(date(month_end_day_date_converted)=Max(date(month_end_day_date_converted)),1,0) as calendar_current_month,
//if(date(month_end_day_date_converted)>=(today()-10) and date(month_start_day_date_converted)<=(today()-10),1,0) as calendar_current_month,
if(date(month_end_day_date_converted)>=(today()-1) and date(month_start_day_date_converted)<=(today()-1),1,0) as calendar_current_month,
//if(month_end_day_date_converted>=YesterdaysDate and month_start_day_date_converted<=YesterdaysDate,1,0) as calendar_current_month,
//if(calendar_year_month='201606',1,1) as calendar_current_month,
RowNo() as calendar_year_month_rank
Resident D_MONTH_END_DATE_TEMP
order by month_end_date_key desc;
Drop table D_MONTH_END_DATE_TEMP;
//exit script;
//Max:
//LOAD month_end_date_key, Max(month_end_day_date_converted) as MaxMonth, Resident D_MONTH_END_DATE Group By month_end_date_key;
D_MONTH_END_DATE_CURRENT_MONTH:
NoConcatenate
Load
calendar_year_month_rank
Resident
D_MONTH_END_DATE;
//where
//calendar_current_month='1';
Let vCurrentMonth=peek('calendar_year_month_rank',1,'D_MONTH_END_DATE_CURRENT_MONTH');
Drop table D_MONTH_END_DATE_CURRENT_MONTH;
//exit script;
left join(D_MONTH_END_DATE)
Load
month_end_date_key,
calendar_year_month_rank-$(vCurrentMonth) as calendar_current_month_offset
Resident
D_MONTH_END_DATE;
Store D_MONTH_END_DATE into
and in KPI
if(GetSelectedCount([Month])=0 and GetSelectedCount([Fiscal Year])=0,
//Currently, defaulted to last month until current month data is validated and tested (current month would be offset of -1
num(sum({<calendar_current_month_offset={'$(vDefaultMonthABCInventory)'}, month_end_metric_date_key={'$(varMaxDate)'}>}forecast),'#,##0'),
//calendar_year_month = {'$(=Max(calendar_year_month))'}
num(sum({<calendar_year_month = {'$(=Max(calendar_year_month))'}>}forecast),'#,##0')
Also I get the following error when I try to debug it.
Did you just copy and paste what I wrote. :-). It was an example: probably this would give more clarity
PUT this script after you calendar table
Temp:
Load
Min(month_end_date_key) as minDate,
Max(month_end_date_key) as maxDate
Resident 'Your calendar table where you have field month_end_date_key';
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
After doing this I got value in KPI as 4M but the correct value is 6.25M
Did you check the value of $(varMaxDate).
Probably you need to check the variables in your equation
I checked the date it comes as 20170131. I need the date of 20161031
I thought you were looking for the max date. You need to modify the peek or
the field to get the result
What exactly should be the modification for peek function?
I don't know if you are looking for the max dates or dates in between, best would be use Order by Dates below
Load
Min(month_end_date_key) as minDate,
Max(month_end_date_key) as maxDate
Resident 'Your calendar table where you have field month_end_date_key' order by month_end_date_key asc ;
and also read about peek below