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!!!

[sunny_talwar · Accepted solution]

May be this?

Above(Sum({<fiscal_quarter>}accessories_revenue), 4) * Avg(1)

[suryajeganathan]

Hi Sunny

Tanq so much for your reply.

It's exactly what i want !!! apart from the below issue

Ex:

FY19'Q2 is the current running financial quarter which has hardly 45 days of data & which is getting compared with the entire FY18'Q2. So how to restrict the last Year QTD depends on the current QTD.

[sunny_talwar]

May be create a flag like this in the script which flags the first x days for all the quarters and then you can use that in the set analysis in combination with an if statement which says if current quarter... then use the set analysis to show first x days, other wise show all days of the quarter.

[suryajeganathan]

Hi Sunny

Tnq for your quick reply. But this app has 120 mb of data so if i write a flag in a script i have to go for a data load again.

So Is there any other possible method Which i can perform in the dashboard layer?

[sunny_talwar]

You don't want to reload? Is this app never going to be reloaded again?

[suryajeganathan]

I have to reload but i thought of skipping that step for now

[sunny_talwar]

I don't think what you are seeking is going to be simple and doing this without modifying the script will make it even more difficult. I don't think I have the time to figure this out, but someone else might have an idea.

All the best,

Sunny

[suryajeganathan]

No problem...Tanq so much for your help

[suryajeganathan]

Hi Guys

Below mentioned expression is working fine ,which is also giving right output for Current QTD vs Last QTD.

But when i filter for the current quarter as FY19'Q2 its again summing up.

=Above(Sum({<fiscal_quarter,Date ={"<=$(=date(AddYears(Today(),-1)))"}>}accessories_revenue), 4) * Avg(1)

[sunny_talwar]

Your expression seems to work for me