6 Replies Latest reply on Oct 2, 2017 12:43 AM by keerthana kumar

show Quaterly as max Qtr month value & Yearly as max month value

I have the below monthly Inventory data

My data is shown as per Fiscal Year [April -March]. Eg :FY 2016 -   April 2016 to March 2017

My date formulas

Date :  DATEPARSE('yyyyMM',[Period])

FY Date : DATEADD('year',-1,[Calc Date]) [Fiscal Year Start --> April]

when shown at Quarterly Level i don't want Sum(Inventory) instead show per quarter max(month) inventory

Eg .FY16

Q1 is June16 Inventory value,

Q2 -->Sept 16

Q3 --> Dec16

Q4 -->Jan 17

when shown at Year Level

FY16 -->March2017 inventory value

FY17 -->March2018 inventory value

When drilled Up to Quarter level I don't want Inventory value to get summed up instead show Respective Quarter max month value as Quarterly Value

and Yearly=FY max month value

as shown in excel tables

Shinichiro Murakami

• 1. Re: show Quaterly as max Qtr month value & Yearly as max month value

Hi Keerthana,

Find my approach as reference below and stored in attached workbook version 10.2 located in the original thread.

1. Year View

2. Quarter View

3. Month View

4. date > right click > text menu > Default Properties > Fiscal Year Start > Check April

5. Max Year: if date={Fixed [date (Years)]:max([date])} then [date] END

6. Max Quarter: if date={Fixed [date (Quarters)]:max([date])} then [date] END

7. Max Month: if date={Fixed [date (Month / Year)]:max([date])} then [date] END

8. Create parameter for sheet selection

9. Define filter for each sheet Year, Quarter and Month

10. Drag required objects to the indicated locations on the Year, Quarter and Month  sheet

Regards,

Norbert

• 2. Re: show Quaterly as max Qtr month value & Yearly as max month value

Hi Keerthana,

Another approach without parameter.

Still needs to set fiscal start month.

Plus, need to create another date field to judge end of period with Fiscal dates.

Now you got.

Then, swap the Bar with drill down/up

Then,  "Inventory3" as red

Thanks,

Shin

<< Ambassador Spotlight Here ! >>

• 3. Re: show Quaterly as max Qtr month value & Yearly as max month value

Thank you so much for looking into my scenario.  I was able to achieve it with parameters but my client is looking for something to do with drill down option "+" and "-".They don't want to use parameter. They are looking for something like below.

Is there any way we can achieve it as below. And my  fiscal year calculation FY 2017 is not from April2016 -March 2017 its April2017 - March2018

• 4. Re: show Quaterly as max Qtr month value & Yearly as max month value

Hello Shin,

Thanks again for looking into my issue. Yes i'm looking for something like this without parameter. But how will i give the logic to display "Y inventory" when its at Year level."Q inventory"  when drilled down at quarter level. i don't want to display each level at different axis. can we achieve it as below

• 5. Re: show Quaterly as max Qtr month value & Yearly as max month value

Hi Keerthana,

Sorry I don't understand your question well.

Simply drill up/down....

Thanks,

Shin

1 of 1 people found this helpful
• 6. Re: show Quaterly as max Qtr month value & Yearly as max month value

This is exactly what i was looking for.