Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gaurav_pandey14
New Contributor II

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

Re: Error While Using Variable in Script

Hi Gaurav, things you can check:

1. Measure content after reload: Check that the $(VPreviousMonthDate) is still in the expression and hasn't been expanded in the reload.

- If measures are loaded as variables: check in Edit mode-> variables button on left-bottom corner

- If loaded as field values: add as filter to check the loaded values

2. VPreviousMonthDate value: Add VPreviousMonthDate to a text box or table to check the value returned

3. $-Expansion and date format: Add the measuse to a table and replace $(VPreviousMonthDate) with the exact value shown for the variable to check if it works as expected.

gaurav_pandey14
New Contributor II

Re: Error While Using Variable in Script

Hi Ruben,

Thanks for quick response.

1:- It showing wrong value when i have used $(VPreviousMonthDate) in text box.


30 December 1899.


please look attached screen shot.Value of Variable.PNG

sasiparupudi1
Honored Contributor III

Re: Error While Using Variable in Script

Try your variable like

Let vXX="Sum({ <Tab={'Actual'},Head={'Seats'},LinkDate={'$"&"(VPreviousMonthDate)'}> }  Data )";

Re: Error While Using Variable in Script

Hi Guarav, that's the value for Date(0), is 0 the max value for LinkDate? try to add the max date as an expression to check the value returned:

=max(LinkDate)

In variable editor, wich one is the content of the variable?: The expression or the string '30 December 1899'? Maybe it's being calculated in the script and it shouldn't, in that case change 'LET' to 'SET' in variables declaration. (Only if variable is generated while reloading)

gaurav_pandey14
New Contributor II

Re: Error While Using Variable in Script

Thanks,

Should I use this main script (refer above script) or variable declaration (refer above script).

Please suggest.

sasiparupudi1
Honored Contributor III

Re: Error While Using Variable in Script

in your load script

gaurav_pandey14
New Contributor II

Re: Error While Using Variable in Script

Sorry BUT I am little bit confuse what should i do.

In variable editor, wich one is the content of the variable?

The expression or the string '30 December 1899'? Expression.


Please suggest??


gaurav_pandey14
New Contributor II

Re: Error While Using Variable in Script

Ok.

Actually I am new in Qlik Sense Development.

How can i use this Variable in Output Screen or in  Measures to check on UI??

Re: Error While Using Variable in Script

and max(LinkDate) what returns? Add in a table the expression to check that it returns a value > 0

Can you upload a sample qvf to check?