Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
suryajeganathan
Contributor III
Contributor III

Fix required in QTD formula

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

1 Solution

Accepted Solutions
sunny_talwar

May be this?

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

Capture.PNG

View solution in original post

21 Replies
PrashantSangle

try below

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

accessories_revenue)

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
suryajeganathan
Contributor III
Contributor III
Author

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
suryajeganathan
Contributor III
Contributor III
Author

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
MK9885
Master II
Master II

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
Master II
Master II

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

Provide the data in XL format.

suryajeganathan
Contributor III
Contributor III
Author

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
Master II
Master II

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