Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
Am facing the issue in expression can any one correct me? in pivot table
thanks in advance
i dnt hve master calender....
my script lik dis:
LOAD
PRJ_NUMBER,
PERIOD_NAME,
BALANCE_ACTUAL,
BALANCE_BUDGET,
PERIOD_YEAR,
Year(PERIOD_YEAR) as CYear,
Month(PERIOD_YEAR) as CMonth,
Date(PERIOD_YEAR) as CDate,
PERIOD_NUM,
PRJ_BU
FROM [lib://Management/MANAGEMENT_PACK.QVD]
(qvd);
current month- Sum({$<Month = {$(=Max(Month))},Year = ,Quarter = ,Month = >} BALANCE_ACTUAL)
6months[til jun'15] -- Sum({<Month = {"<=$(=max(Month, 6))"},Year = {$(=(max(Year)-1))} >}BALANCE_ACTUAL)
Ytd[til dec'15- Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} BALANCE_ACTUAL)
where am geting wrng values so kindly help me out where am getting problem
thanks
suresh
this wud be your new expression with new Period_Number field:
=Sum({<Period_Number = {'$(=Max(Period_Number))'},Year = ,Quarter = ,Month = >} BALANCE_ACTUAL)
am getting error while creating in scriptng bro...
as calenderyear and calender mnths not found
Post a sample data, either QVD or Excel..
Dont put QVF as I dont have Qliksense
CalenderYear*12+month(CalenderMonth) as Period_Number
In your script, Replace CalenderYear with a field which contain values like 2014,2015 etc
Replace CalenderMonth with a field which contain values like Jan,Feb,Mar etc
hi
PFA
this my qvd bro..
please help me on this
thanks
suresh
Use the following script:
LOAD *,
CYear * 12 + CMonth As PERIOD_NO;
LOAD
PRJ_NUMBER,
PERIOD_NAME,
BALANCE_ACTUAL,
BALANCE_BUDGET,
PERIOD_YEAR,
Year(PERIOD_YEAR) as CYear,
Month(PERIOD_YEAR) as CMonth,
Date(PERIOD_YEAR) as CDate,
PERIOD_NUM,
PRJ_BU
FROM [lib://Management/MANAGEMENT_PACK.QVD]
(qvd);
And then use the following expressions:
=Sum({$<Month = {"$(=Max(PERIOD_NO))"},CYear=, CQuarter=, CMonth=>} BALANCE_ACTUAL)
=Sum({$<Month = {">=$(AddMonths(Max(PERIOD_NO), -6))"}, CYear=, CQuarter=, CMonth=>} BALANCE_ACTUAL
=Sum({$<Month = {">=$(Max(PERIOD_NO) - 6)"}, CYear=, CQuarter=, CMonth=>} BALANCE_ACTUAL
thanks jonathan,
As i used above script and expresn for actual
as it shwng same values there is no diff in values please chk below image
check for Actual and Actual HY u hve given dat expression
Hi Suresh,
Your PERIOD YEAR field contains only year. You need to alter your script, so that the expressions will work. I just saw your QVD file and modified the script accordingly. You can use above set analysis, once you changed.
Data:
Load*,
'Q' & ceil(month(Date)/3) as Quarter;
LOAD PRJ_NUMBER,
PERIOD_NAME,
BALANCE_ACTUAL,
BALANCE_BUDGET,
PERIOD_YEAR,
PERIOD_NUM,
PRJ_BU,
Makedate(PERIOD_YEAR,Month(Date#(PERIOD_NAME,'MMM-YY')),Date(PERIOD_NUM)) as Date,
Month(Date#(PERIOD_NAME,'MMM-YY')) as Month,
PERIOD_YEAR as Year
FROM
MANAGEMENT_PACK.QVD
(qvd);
Let me know.
thnks, nagraj.
i am wrkng on this regarding varience for 6mnth actual abd budjet
and variance for ytd of actual and budjet..?
for how can i write the exprn in qliksense?
thanks
Suresh
Sorry.. I didn't get a chance to use Qliksense. Did you try the above expressions!! You got a date, month, quarter, year fields. I guess it should work..