6 Replies Latest reply on Apr 2, 2018 7:08 AM by Saikrishna Mamidi

# Balance by end of Quarter

Hi,

I have requirement to show Date drill down,

Suppose If my Data is as follows

Date                    Value

 1/1/2018 38 2/1/2018 84 3/1/2018 58 4/1/2018 86 5/1/2018 69 6/1/2018 53 7/1/2018 43 8/1/2018 61 9/1/2018 35 10/1/2018 86 11/1/2018 64 12/1/2018 70

I would like to see as below if Drill down to Quarter,

Q1- 58

Q2-53

Q3-35

Q4-70

If I drilldown to month,  it should show full data as on top. Is it possible to do with any QTC.?   Or Do we need to write any complex calculation.   Pl let me know if anyone have a solution.  Thank you in Advance.

• ###### 1. Re: Balance by end of Quarter

STEP 1

Makedate=

MAKEDATE( INT(RIGHT(STR([Date]),4)),

INT ( LEFT(STR([Date]),2) ),

INT( MID(STR([Date]),4,2))

)

Step 2

Create Index

and drag it to Rows & Compute Using

STEP 3

Filter INDEX =3

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 2. Re: Balance by end of Quarter

Oh So you need to DRILL DOWN as well....

Find my new approach then

Just one change here

Make a new caclulation , DRAG TO FILTER and set as TRUE

Please find solution sheet as attached.

Please mark this thread as CORRECT / HELPFUL if it really helped you so that it can help others as well.

Thanks,

Ritesh

2 of 2 people found this helpful
• ###### 3. Re: Balance by end of Quarter

Hi Ritesh,

Thank you for solution,  It helped me to find end of quarter values.  But If I drill down, it didn't work as expected.

And another thing is that, I would like to show the data as Stacked bars (not like cross tab).  I do have category dimension added in attached sheet.  And generally my underlying data would be limited to 2 years. they would like to see as continuous Stacked bar chart, There will be another parameter, I have added as reference line in the work book. Users will change the value to move the reference line

The values showing in attached sheet is summing up (sum of month in a quarter).  If possible can you pl help me with solution in attached sheet.

Thank you.

• ###### 4. Re: Balance by end of Quarter

Hello Sai,

VIEW 1

VIEW 2

Hope it helped !

File is attached (10.3 Version ) ---Check Sheet name as PART 1

Let me know if it helped you.

Thanks,

Ritesh

2 of 2 people found this helpful
• ###### 5. Re: Balance by end of Quarter

Please mark this thread as CORRECT / HELPFUL if it really helped you so that it can help others as well.

Thanks,

Ritesh

• ###### 6. Re: Balance by end of Quarter

Thanks for you solution.  I am looking exactly this.  But still another functionality to be applied .     I need another vertical reference line where *** can move based on Date parameter.

The reason for this visual is, The underlying data is  "Actuals (till date)  + Forecast"  all together for full 2 calendar years, the vertical reference line is used to cut off the actual s and use forecast ed value for the remaining months,  (The date may be current month or 1 or 2 previous months).

If I make Date as discrete, I am unable to add vertical reference line, So in my workbook, I changed the Date as continuous to make Date parameter to slide across.