Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
gaurav_pandey14
Contributor III
Contributor III

Error While Using Variable in Script

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.


17 Replies
gaurav_pandey14
Contributor III
Contributor III
Author

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:

Sin título.jpg

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

gaurav_pandey14
Contributor III
Contributor III
Author

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.

gaurav_pandey14
Contributor III
Contributor III
Author

Hi Ruben,

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

gaurav_pandey14
Contributor III
Contributor III
Author

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.