Fix required in QTD formula

[suryajeganathan]

Hi

I have a Fiscal Quarter in FY18'Q4 format. I am using the below formula to calculate the dynamic QTD.

=SUM({< fiscal_quarter, Date ={">=$(=date(QuarterStart(Today(),-4))) <=$(=date(AddYears(Today(),-1)))"}>}

accessories_revenue)

For ex while selecting FY18'Q4 it has to show the QTD data for FY17'Q4. But its not giving this output

Please help me to resolve this

Thanks in advance!!!

[PrashantSangle]

try below

=SUM({< fiscal_quarter, Date ={">=$(=date(AddYears(QuarterStart(Today(),-4),-1))) <=$(=date(AddYears(Today(),-1)))"}>}

accessories_revenue)

Regards,

[suryajeganathan]

Hi Prashant

Tanq for your reply!!!

But your expression also giving wrong output...Below mentioned is the expression

=SUM({< fiscal_quarter,

Date ={">=$(=date(AddYears(QuarterStart(Max(Date),-4),-1)))<=$(=date(AddYears(Max(Date),-1)))"}>}

accessories_revenue)...

[PrashantSangle]

take text object and check what is output of

1: =date(AddYears(QuarterStart(Max(Date),-4),-1)))

2: =date(AddYears(Max(Date),-1))

3: check date format

And what do you mean by giving incorrect result?? Please be more specific.

Regards,

[suryajeganathan]

Hi Prashant

Attached is the app for your reference ....

I am expecting to show the current quarter vs the last year quarter in a table.As well when the filter for the different quarter the last year quarter filters out automatically depends on the selection.

[PrashantSangle]

120 mb file .... do you have any reduced file..

Regards,

[MK9885]

Surya,

Try below expression, I use this in one of my report...

Before that you should have these fields....

Num_Month is months in numeric form like 01,02, 03.....12

Year should be numeric as well, 2017, 2018 etc

Quarter is your Q1, Q2 etc

sum({<Num_Month = { '$(=Max(Num_Month) -2 )'}, Year = {  $(=Max(Year) - 1)},Month

,Month = {"<=$(=num(Month))"}, Quarter=p(Quarter)

>+< Num_Month = { '$(=Max(Num_Month) -1 )'},Year = {  $(=Max(Year) - 1)},Month

,Month = {"<=$(=num(Month))"}, Quarter=p(Quarter)

>+<

Num_Month = { '$(=Max(Num_Month)   )'},Month,

  Year = {  $(=Max(Year) - 1)} ,

    > }accessories_revenue)

[MK9885]

I checked your application, it would need more fields in Calendar...

Provide the data in XL format.

[suryajeganathan]

Hi Mohammed,

I have attached the Sample file with two years of data.

The problem is with the Fiscal_Quarter format its not in Q1,Q2 etc format.It's in FY19'Q1 format that is the reason the above mentioned formula is not giving the right output.

Can u plz elaborate your suggestion in detail?

[MK9885]

Normally the expression I gave above should work regardless of year/month/quarter selection...

In your case your 2018 Q4 is Jan, Feb & Mar while 2018 Q1 is Apr, May & Jun.

A calendar can be made from your week_start field but Q1 from taht will start from Jan to Apr not Apr to Jun.

I think FYQ4 would be next year Q1 I.E FY 18 04 = 2019 Q1 but here Q4 = Q1.

Not sure if I can give you the answer or understood it correctly... will try meanwhile I hope someone with expertise level can help you?

stalwar1‌