Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have Quarter Year field in this format, 20183, 20182, 20181, 20174, 20173, 20172, 20171 and so on.
I would like to create a variable for the the previous quarter calculation.
So, When the user select 20183 they will see the current Quarter value and 20182 will be the previous Quarter value.
I was trying with this: =Left(QuarterYear,4)&(Right(QuarterYear,1)-1) but when i select 20181 its giving me 20180 rather than 20174.
Any help?
Hi,
For a selected date you can calculate the previous quarter as:
'Q' & If(Ceil(Month(Date)/3)-1=0,4,Ceil(Month(Date)/3)-1)
Ag+
Hi Ag+,
Thanks for your reply but its not giving the desired results.
When i select 20171, 20172, 20173 its showing quarter Q4.
Thanks,
BC
Sorry, i was thinking on standard quarter format ...
Use
If(Ceil(Month(Date)/3)-1=0 ,Year(Date)-1 & 4 ,Year(Date) & Ceil(Month(Date)/3)-1 )
Ag+
Please see attached file. i am still not getting the desired results. In the text box i add your calculation.
Thanks,
BC
I suggest using QuarterStart() function to create a quarter field which can then be used in set analysis.
LOAD QuarterStart(Date) as QuarterYear
and in your set analysis you can do this
Sum({<QuarterStart = {"$(=QuarterStart(Max(QuarterYear), -1))"}>} Measure)
Hi Sunny I do not have a Date field, I have a Quarter Year field only. The example values are as i mentioned in my previous post like in this format, 20183, 20182, 20181, 20174, 20173, 20172, 20171 and so on.
Thanks,
BC
Create a QuarterYear field like this
=QuarterStart(MakeDate(Left(QuarterYear, 4), Right(QuarterYear, 1)*3)) as QuarterYear_New
and then use the set analysis on the QuarterYear_New field.
Hi Sunny,
What is this QuarterStart field in your set analysis that you are comparing to?
Sum({<QuarterStart = {"$(=QuarterStart(Max(QuarterYear), -1))"}>} Measure)
Thanks,
BC
My bad... should be QuarterYear
Sum({<QuarterYear = {"$(=QuarterStart(Max(QuarterYear), -1))"}>} Measure)