17 Replies Latest reply: May 2, 2018 9:44 AM by Ruben Marin RSS

    Error While Using Variable in Script

    Gaurav Pandey

      LOAD SCRIPT:-

      Headcount:

      LOAD

          'Actual' as Tab,

          "Cost Center",

          "Customer Group",

          "Delivery Center",

          "Delivery Country",

          Geography,

          "VLOB (FY1718)",

          "HLOB (FY1718)",

          "Vertical without CE FY'17",

          "SBU (FY1718)",

          Head,

          Data,

          Date([Reporting Date]) as LinkDate

      FROM [lib://ProcessPL_File (del-sar-qlprod_qlikadmin)/HeadcountORSeat*.xlsx]

      (ooxml, embedded labels, table is Actual);

       

       

      LOAD

      'Plan' as Tab,

          "Cost Center",

          "Customer Group",

          "Delivery Center",

          "Delivery Country",

          Geography,

          "VLOB (FY1718)",

          "HLOB (FY1718)",

          "Vertical without CE FY'17",

          "SBU (FY1718)",

          Head,

          Data,

           Date([Reporting Date]) as LinkDate

      FROM [lib://ProcessPL_File (del-sar-qlprod_qlikadmin)/HeadcountORSeat*.xlsx]

      (ooxml, embedded labels, table is Plan);

       

      ----------------------------------------------------------------------------------------------------------------

      VARIABLES DECLARATION:-

      --------------------------------------------

      =Date(monthend(AddMonths(max(LinkDate),1)),'DD/MM/YYYY')

      ------------------------------------------------------------------------------------------------------------------------------------------


      VALUE LIST (Columns):-

      -------------------

      Valuelist(Date((VPreviousMonthDate),'MMM-YY')&'Actual' &'@Reported Currency',

      Date((VPreviousMonthDate),'MMM-YY')&'Actual' &'@Plan Currency',

               Date((VPreviousMonthDate),'MMM-YY')&'Plan' &'@Plan Currency',

               Date((VMaxDate),'MMM-YY')&'Actual' &'@Reported Currency',

               Date((VMaxDate),'MMM-YY')&'Actual' &'@Plan Currency',

               Date((VMaxDate),'MMM-YY')&'Plan' &'@Plan Currency')

      ----------------------------------------------------------------------------------------------------------------------------------------------------

      MAIN SCRIPT (Measures):-

      --------------------------------------

      Pick(Match(Valuelist(Date((VPreviousMonthDate),'MMM-YY')&'Actual' &'@Reported Currency',

      Date((VPreviousMonthDate),'MMM-YY')&'Actual' &'@Plan Currency',

      Date((VPreviousMonthDate),'MMM-YY')&'Plan' &'@Plan Currency',

      Date((VMaxDate),'MMM-YY')&'Actual' &'@Reported Currency',

      Date((VMaxDate),'MMM-YY')&'Actual' &'@Plan Currency',

      Date((VMaxDate),'MMM-YY')&'Plan' &'@Plan Currency'),Date((VPreviousMonthDate),'MMM-YY')&'Actual' &'@Reported Currency',

      Date((VPreviousMonthDate),'MMM-YY')&'Actual' &'@Plan Currency',

      Date((VPreviousMonthDate),'MMM-YY')&'Plan' &'@Plan Currency',

      Date((VMaxDate),'MMM-YY')&'Actual' &'@Reported Currency',

      Date((VMaxDate),'MMM-YY')&'Actual' &'@Plan Currency',

      Date((VMaxDate),'MMM-YY')&'Plan' &'@Plan Currency'),

      Sum({ <Tab={'Actual'},Head={'Seats'},LinkDate={'$(VPreviousMonthDate)'}> }  Data ),

      Sum({ <Tab={'Actual'},Head={'Seats'},LinkDate={'$(VPreviousMonthDate)'}> }  Data ),

      Sum({ <Tab={'Plan'},Head={'Seats'},LinkDate={'$(VPreviousMonthDate)'}> }  Data ),

      Sum({ <Tab={'Actual'},Head={'Seats'}> }  Data ),

      Sum({ <Tab={'Actual'},Head={'Seats'}> }  Data ),

      Sum({ <Tab={'Plan'},Head={'Seats'}> }  Data ))


      NOTE:- Highlighted variable not working. But without using variable its running fine.


      It would very appreciable if anyone help me out.


      Also let me know if any clarification needed.