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.

[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.

[Anonymous]

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.

[sasiparupudi1]

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)

[Anonymous]

Thanks,

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

Please suggest.

[sasiparupudi1]

in your load script

[Anonymous]

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

[Anonymous]

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?