4 Replies Latest reply on Sep 14, 2016 1:02 PM by tanya.kanwar.0

# QTD Issue while using parameters

Hi,

I have a month parameter "T1_Month Selection" that takes derive its value from Report date and another parameter called "Views" where user can select Monthly,QTD and YTD value.

And my fiscal year is set to Nov.

I have a calc field to calculate the actuals based on the two parameters user select. The calc field has belowformulae:

If [Views]="Monthly"

then

(

if [Report Date]<= [T1_Month Selection]

and

DATEDIFF('month',[Report Date],[T1_Month Selection])=0

then [Actual]

END

)

ELSEIF [Views]= "QTD"

then

(

if [Report Date]<= [T1_Month Selection]

and

DATEDIFF('quarter',[Report Date],[T1_Month Selection])=0

then [Actual]

END

)

ELSEIF [Views]= "YTD"

then

(

if [Report Date]<= [T1_Month Selection]

and

DATEDIFF('year',[Report Date],[T1_Month Selection])=0

then [Actual]

END

)

END

I created a sample workbook. When my user selects monthly, it should display monthly value and for QTD, Nov, Dec and Jan as Qtr1( have set the fiscal year to Nov) .

Can anyone help..?

• ###### 1. Re: QTD Issue while using parameters

Hi Tanya,

Please try the below calculation and check if you get the expected results

case [Views]

WHEN "YTD" THEN DATENAME('year',[Report date])

WHEN "QTD" THEN "Q" + STR(INT(DATENAME('quarter',[Report date]))-3) + "-" + STR(YEAR([Report date]))

WHEN "Monthly" THEN DATENAME('month',[Report date]) + "-" + STR(YEAR([Report date]))

END

Regards,

Sreekanth

• ###### 2. Re: QTD Issue while using parameters

Sreekanth, how the actual value will change as per the views..?

Can u please make change in the workbook..?

• ###### 3. Re: QTD Issue while using parameters

The calculation will only be used for display put it in column shelf and let us know the results you can use the same calculation using currently for actuals

• ###### 4. Re: QTD Issue while using parameters

It displays all the month. I want only the value for the month selected by the month selector parameter and view parameter. See the attached workbook. I have made the change.