RE:Current,6months,ytd

[Chanty4u]

hi all,

I hve a pivot table.,

fields i hve year, month, date,

and i hve project id aswell...wen i select on one prjcet

i need  current month details

and 6mnoths

and for YTD

how can i achieve this?

help me on this.

thanks in advance.

Suresh

[HirisH_V7]

Hi suresh you can use ,

This expression for 6 months ,

in frontend

Date = {">=$(=MonthStart(Max(Date), -11))<=$(=MonthEnd(Max(Date)))"}

in Back end

If(Date>= AddMonths(Today(), -6), 1)as Last6MonthFlag

Last6MonthFlag={1}

Hope this helps!!

Thanks,

Hirish

[Chanty4u]

thanks  hirish,

But i need in front end only...  ihve tried some other expresions in my table..but it shwng same values..

and it nt shwng months in my filter...

PFA for qvf and  image for exct...

i tried ur exp also nt getting...

thanks in advandce..

Suresh

[HirisH_V7]

hi,

Can you make your requirement more clear .

Thanks,

Hirish

[Chanty4u]

i hve pivot table..

with in that i hve project date and

Actual and budjet and varience means  actual-budjet..

so need the values actual and budject for current month and for 6months and fr YTD

so i had done almost all but getting some values same could you chk my expressions where the errors i hve?

i need to show the table as i attached imge in above msg

please chk below updated qvf..

thanks for ur response

Suresh

[Chanty4u]

hi   hirishv7‌

any update on this?

any one

thanks

Suresh

[Chanty4u]

could  anyone look into this?

need help on this.

Thanks in advance

suresh

[miskinmaz]

Hi,

The way you are calculating the cdate and cyear on period year, the result you are getting is wrong. Rather you can use

MonthStart(PERIOD_NAME) to calculate the cdate and cyear. Also in the expression you are  using the date field which is not present in your model use cdate.

[Anonymous]

Suresh,

For your Current Month, you can use following expression:

CurrentMonth=sum({<CalenderYear=,Qaurter=,Day=,CalenderMonth={'$(=max(CalenderMonth)')>} sales)

YTD=sum({<CalenderYear={'$(=max(CalenderYear)')>} sales)

Hope this will help!!

[Chanty4u]

thanks

but i dont hve mastercalender?

how can i achieve?

is dere to do any chnges in script?

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);

the above is my data