How can we fix Hardcoding in Visualisation??

[#1]

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')

)

[phaneendra_kunc]

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))'}

[#3]

Do I need to add max(month_end_date_key) in place of 20161031?

[#4]

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')

)
   

[rupamjyotidas]

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)'}

[#6]

I got zero value in KPI after keeping max(month_end_date_key)

[#7]

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 .

[#8]

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 (qvd);

[rupamjyotidas]

What is the Value coming in $(varMaxDate)

[#10]

How can we check the value for $(varMaxDate)?