Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
suryajeganathan
Contributor III
Contributor III

Last Year QTD

Hi

I am using the below QTD formula for the Last Financial Quarter. When i filter out for the different quarter it has to show the respective last quarter data. But its not showing right . Logically for the current fiscal quarter i have almost 2 months data so even for the prior QTD it has to take only 2 months like the current FY quarter. But its taking entire last quarter data.

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

accessories_revenue)

Fiscal Quarter Format : FY16'Q1


Please help me fix this?

Thanks in Advance

10 Replies
dwforest
Specialist II
Specialist II

Your expression tells it to take up to the end of the current quarter - 4, not up to today date of last year.

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

accessories_revenue)

suryajeganathan
Contributor III
Contributor III
Author

Hi David,

Thank you so much for your quick reply.

It's working for the last year QTD. But when i select the other Fiscal Quarter filter Its showing the same result of Last year QTD.

I want this to work dynamically when i select the other fiscal_quarter also.

Can you plz help me achieve that?

Anonymous
Not applicable

Create a Table with all Quarter and a QuarterID (incremental integer) and than select the possibile quarter dynamicaly, somthing like this:

[QuarterId] = {$(=concat([QuarterId] - 1, ‘,’))}


suryajeganathan
Contributor III
Contributor III
Author

Hi Barbolini

I am looking for something handy ....

For Table creation i have to again go for the data load Due to 50 M data it will take lots of time for me to make the changes.

Can u plz suggest some other option?

Anonymous
Not applicable

Starting from David exemple 


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

accessories_revenue)


The problem is “Today()” put somthing like “Max([YourDataFiel])” so this is dynamic based on selection, can this solve your problem? 

suryajeganathan
Contributor III
Contributor III
Author

Yes that helps...Tanq so much

suryajeganathan
Contributor III
Contributor III
Author

Hi

This expression is taking YTD  for all the quarter. But the values are not as expected . Is that the problem with the format of the Quarter column FY17'Q4?.


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

accessories_revenue)

suryajeganathan
Contributor III
Contributor III
Author

Hi Guys

I think the expression finding difficult to understand the format of the Quarter.

Can anyone help me to fix this issue asap?

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

accessories_revenue)

suryajeganathan
Contributor III
Contributor III
Author

The quarter column in the below format

FY17'Q2