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
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
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
rubenmarin

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
Contributor III
Contributor III
Author

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
Master III
Master III

Try your variable like

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

rubenmarin

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
Contributor III
Contributor III
Author

Thanks,

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

Please suggest.

sasiparupudi1
Master III
Master III

in your load script

gaurav_pandey14
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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??

rubenmarin

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?