dispaly total year

[soniasweety]

Hi all,

I have a data for fiscal year full  Aprl to march

in my pivot table am showing plan and actual for current month

but my requirement is that  I have a plan values total fiscal year  but I have actual values till current month only

I want to show all the plan values total fiscal year and actual should show zero.

using this expression now  :  sum({<Fiscal Year]={$(=max([Fiscal Year]))}, MonthNo = {"<=$(=max(MonthNo))"},[Month Name]=,Month=>} [PAmount])

from above I want to show total data not for current month

[Anil_Babu_Samineni]

Try this way?

If(MonthNo <> Max(TOTAL MonthNo), sum(TOTAL {<Fiscal Year]={$(=max([Fiscal Year]))}, MonthNo = {"<=$(=max(MonthNo))"},[Month Name]=,Month=>} [PAmount]), sum({<Fiscal Year]={$(=max([Fiscal Year]))}, MonthNo = {"<=$(=max(MonthNo))"},[Month Name]=,Month=>} [PAmount]))

[soniasweety]

thanks anil,

but I am getting  2016 to oct2017   but I need  2017arpl to 2018 mar

and for your expression  all months plan data showing same number

[Anil_Babu_Samineni]

Not sure, I followed you. But to answer i would suggest to share some inputs related monthno? How it looks like.

[soniasweety]

here it is

[Anil_Babu_Samineni]

That means Monthno doesn't have Months. First build the Fiscal Year and then use only Fiscal Fields to get into work

[kamalqlik]

Hi Sony,

First you have to create Fiscal year at backend and then use the expression which Anil have shared

Set vFM = 4 ;                                                          // First month of fiscal year

Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year
         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month
          *;
Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year
         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month
          *;
Load Year(MonthNo)                              as Year,           // Your standard master calendar
         Month(MonthNo)                            as Month,
       resident your table containing monthno or date

[soniasweety]

we don't have master calendar

am using this in script for creating  MonthNo

"Month Name",

     Num(MonthName("Month Name")) as MonthNo,

Month Name values are   apr 2016 may 201 6  til mar 2018  like that

fiscal year is derived like below

     if(left("Month Name",3)='Jan' or left("Month Name",3)='Feb' or left("Month Name",3)='Mar',

     Right("Month Name",4)-1,Right("Month Name",4)) as "Fiscal Year",

[kamalqlik]

Hi Sony,

You can use updated script as per my comment above :

Num(MonthName("Month Name")) as MonthNo

Set vFM = 4 ;                                                          // First month of fiscal year

Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year
         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month
          *;
Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year
         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month
          *;
Load Year(Month Name)                              as Year,           // Your standard master calendar
         Month(Month Name)                            as Month,

Num(MonthName("Month Name")) as MonthNo
       resident your table containing monthno or date

Then you can use the expression as per above case.