Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ??
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.
Hi Ruben,
PFA. Kindly look into this issue , if you can help.
thanks in advance!!
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.
Hi Ruben,
Sorry to say but problem is still persist after suggested changes.
Update version.
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).
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.