23 Replies Latest reply: Oct 20, 2016 3:17 AM by Sumit Mitra RSS

    How can we fix Hardcoding in Visualisation??

    Bhawana Adhikari

       

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

       

      )

         

        • Re: How can we fix Hardcoding in Visualisation??
          Phaneendra Kunche

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

          • Re: How can we fix Hardcoding in Visualisation??
            Rupam Das

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

              • Re: How can we fix Hardcoding in Visualisation??
                Bhawana Adhikari

                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 .

                • Re: How can we fix Hardcoding in Visualisation??
                  Bhawana Adhikari

                  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 [D:\ABC\data\supply\slob\D_MONTH_END_DATE.dat]
                  (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 [D:\ABC\data\supply\slob\D_MONTH_END_DATE.qvd] (qvd);