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
yes nagaraj,,its wrked fyn ..but i need how to compare
for actual curent mnth to budjet current mnth
actual 6mnths to current 6mnths
actual ytd to actual ytd.
any suggestions? that wil helps me bro
Thanks
Suresh
do one thing nagaraj... i hve tried in qv also am attachng qvw.... u can give those expr on this qvw?
i tried with some exprsn for currnt and 6mtnhs chk it. and correct
or actual curent mnth to budjet current mnth
actual 6mnths to current 6mnths
actual ytd to actual ytd.
You need to write following expressions:
Actual Current Month Expression
Budget Current Month Expression
Actual 6 month Expression
Budget 6 Month Expression
Actual YTD
Budget YTD/Previous YTD
fo those expressions usng am getting erors..and dat is nt wrkng?
i need variance of them bro...
Sorry bro.. I left the office by 4pm itself and will be back to office by wednesday. I haven't installed Qlikview in my laptop. Maybe someone could help you in the meantime.
thanks bro..np
HI,
Use the Following at script level
Table1:
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);
New_Table:
Load * ,
if(DayNumberOfYear([Trans Date])<=DayNumberOfYear(Today()),1,0) as IsInYear,
if(DayNumberOfQuarter([Trans Date])<=DayNumberOfQuarter(Today()),1,0) as IsInQTD,
if(day([Trans Date])<=day(Today()),1,0) as IsInMTD,
if(Month([Trans Date])<=month(Today()),1,0)as IsCurrentMonth,
if(month(AddMonths([Trans Date],1))=Month(Today()),1,0)as IsLastMonth
Resident Table;
Drop Table Table1;
Ex:
ACTUAL YTD: Sum({IsInYear={'1'}>}BALANCE_ACTUAL)
Budget YTD: Sum({IsInYear={'1'}>}BALANCE_BUDGET)
similarly you can write all
thanks sasi..
but when am loading the below error is shwng?
can u please look into this?
hi sasi,
i have chnged that tranc date to period num its wrkng fyn but
when i use YTD actul above ur expression it is shwng '-' symbols for all values?
could u please chk dis exprsn?