Error While Using Variable in Script

[Anonymous]

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.

[Anonymous]

max(LinkDate) ? 30 January 2018.

I have not much idea how can i share the Sample QVF.

Can U came one webex to check the same if you come.

Please reach me on as +91-8968740423. So that i can share credenils with you.

It would great if it is possible actually by today any how i will complete this.

Please suggest ??

[rubenmarin]

Sorry, but that's not my way of helping and I also have a lot of things to do... To upload a sample you can do a copy of the qvf and do a reload removing/scrambling all sensitive data, then you can upload that qvf in advanced editor:

In bottom right of advanced editor there is a link to Attach.

[Anonymous]

Hi Ruben,

PFA. Kindly look into this issue , if you can help.

thanks in advance!!

[rubenmarin]

Hi Guarav, it's a date format issue:

Link date is M/D/YYYY

Variable is DD/MM/YYYY

Change one of those, both have to be the same format, or use aditional field to apply set analysis.

[Anonymous]

Hi Ruben,

Sorry to say but problem is still persist after suggested changes.

[Anonymous]

Update version.

[rubenmarin]

Hi, seems it worked for me, PFA. You need to select Jan-31, as the variable is adding one month and show february values

To retrieve the last month it should be Addmonths(..., -1), but I'm not sure of your intentions with this, so I kept the Addmonths(..., 1).

[rubenmarin]

Try set the AddMonths of the variable to "-1" instead of "1":

=Date(monthend(AddMonths(max(LinkDate),-1)),'M/D/YYYY')

Don't seems to matter in this case but monthend really return the last time of the last day, to keep only the day (or set to 00:00:00 (the default) you can use:

=Date(floor(monthend(AddMonths(max(LinkDate),-1))),'M/D/YYYY')

Not much use in this case because it's being applied in set analysis as a string.