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
HI,
Might be because of date column, try to change the makedate () format to default qlikview format
can you give your sample data so that we can do some thing which will help you,
thanks sasi,
PFA
My qvd and qvf only one qvd is my total data
u can chk in qvf i tried in diffrnt ways bu i didnt get exctly...
thanks
Suresh
and i tried in qliksense also but nt getting
PFA
HI, Use the Following scipt
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')),day(PERIOD_NUM)) as Date,
Month(Date#(PERIOD_NAME,'MMM-YY')) as Month,
PERIOD_YEAR as Year
FROM
(qvd);
New:
Load * ,
if(DayNumberOfYear(Date)<=DayNumberOfYear(Today()),1,0) as IsInYear,
if(DayNumberOfQuarter(Date)<=DayNumberOfQuarter(Today()),1,0) as IsInQTD,
if(day(Date)<=day(Today()),1,0) as IsInMTD,
if(Month(Date)<=month(Today()),1,0)as IsCurrentMonth,
if(month(AddMonths(Date,1))=Month(Today()),1,0)as IsLastMonth
Resident Data;
Drop Table Data;
Expressions:
Actual YTD: =sum({<IsInYear={'1'}>}BALANCE_ACTUAL)
Budget YTD= sum({<IsInYear={'1'}>}BALANCE_BUDGET)
Find the attachment for reference
HI,
PFA, qlik sense applications
thanks sasi...its wrkng with the values is some wat diffrnce and...where
for 6mtnhs means til jun'15 actual and current mnth is shwng same values?
and how to caluculate variance for actual current and budjet current
and for 6mnths and ytd.... i tied expressions nt wrkng fyn.
Thanks
Suresh
HI,
please let me know what you want to calculate here ,list all your expressions
1)Actaul = sum(Balance_Actual)
2)Budget= sum(Balance_budget)
variance =sum(Balance_Actual)-sum(Balance_budget)
3) What is Actual Hy? what do you want to show though this ?
4) what is Budget hy?
5)Actual YTD: =sum({<IsInYear={'1'}>}BALANCE_ACTUAL)
6)Budget YTD= sum({<IsInYear={'1'}>}BALANCE_BUDGET)
variance is Actual YTD-Budget YTD
sorry sasi..
The expressions i need
1)actual current month and budject current mnth
and varience and for % of both
2)hy means halfyear 6mnths til jun'15 for actual and budjet
and that varience and for % of both
3)ytd means til dec'15
ytd actual and budjet and dat varience and for % of both
thanks ..
For Variance and % try like this:
Variance=sum(TotalCurrentMonth)-sum(BudgetCurrentMonth) //both are interchangeable
%= ((sum(TotalCurrentMonth)-sum(BudgetCurrentMonth))/sum(BudgetCurrentMonth))*100 //or you can set the setting into number format like show number in % if not multiply by 100
similarly for others..
Hope this will help!!
hi SURESH,
Please Check the Attached Application
For Variance % you can take like
(Sum(Curr Actual)-sum(Curr Budget)/(Sum(Curr Actual)+sum(Curr Budget)))*100
Let me know if not