RE:what wrong in my expression?

[Chanty4u]

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

[sasikanth]

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,

[Chanty4u]

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

[Chanty4u]

and i tried in qliksense also but nt getting

PFA

[sasikanth]

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

[sasikanth]

HI,

PFA, qlik sense applications

[Chanty4u]

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

[sasikanth]

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

[Chanty4u]

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

[Anonymous]

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

[sasikanth · Accepted solution]

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