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')
)
try this, not sure the relation you have with these datekey elements.
I am picking the max from your end date key.
month_end_metric_date_key={'$(=max(month_end_date_key))'}
Do I need to add max(month_end_date_key) in place of 20161031?
This is the exact code I have in the Expression
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={'$(vDefaultMonthDiageoInventory)'}, month_end_metric_date_key={'20161031'}>}forecast),'#,##0'),
//calendar_year_month = {'$(=Max(calendar_year_month))'}
num(sum({<calendar_year_month = {'$(=Max(calendar_year_month))'}>}forecast),'#,##0')
)
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'));
month_end_metric_date_key={'$(varMaxDate)'}
I got zero value in KPI after keeping max(month_end_date_key)
I am still getting 0 in KPI after adding
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')); in script
and
month_end_metric_date_key={'$(varMaxDate)'} in data inside measures .
This is what I have in Load script for Month_End_Date:
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
What is the Value coming in $(varMaxDate)
How can we check the value for $(varMaxDate)?