topic Re: Most Recent Quarter Only in QlikView

Most Recent Quarter Only

kaylaramsey2 — Wed, 25 Nov 2020 16:16:04 GMT

I have a bar chart that I want to always show the most recent year-quarter (Jan Feb Mar or Apr May Jun or Jul Aug Sep or Oct Nov Dec). How can I do that?

My dimension is YearMonthAbbreviatedNM

2018-Jan

2018-Feb

2018-Mar

etc.

Re: Most Recent Quarter Only

YoussefBelloum — Thu, 25 Oct 2018 15:36:57 GMT

Hi,

try to load your YearMonthAbbreviatedNM like this:

Date(Date#(YearMonthAbbreviatedNM,'YYYY-MMM'),'YYYY-MMM') as YearMonthAbbreviatedNM

and use a set analysis to filter only on the max YearMonthAbbreviatedNM

Re: Most Recent Quarter Only

kaylaramsey2 — Thu, 25 Oct 2018 16:19:57 GMT

Hi!

I only want to see the most recent last 3 months of the quarter (ex - Q3 Aug, Sep, Oct). Won't the set analysis you suggested just show me the last month?

Here is my expression -

=Num(Round(Count({<TopBoxFLG = {'1'},>}SurveyID)/Count({TopBoxFLG = {'1', '0'}>}SurveyID), .001), '#,##.#%')

Going off what you suggested I think I would need to do a max on the YearQuarterNM (2018-Q3), which is also a dimension I have available.

Would you suggest that? Also how would I represent that in my expression?

Re: Most Recent Quarter Only

lcontezini — Thu, 25 Oct 2018 16:31:57 GMT

Limit your date field with set analysis using quarterstart(today()) and quarterend(today()) in your measure expression, and just use YearMonthAbbreviatedNM as dimension.

Re: Most Recent Quarter Only

kaylaramsey2 — Thu, 25 Oct 2018 16:54:02 GMT

Would I set it up my expression like this? I'm getting an Error in Set Modifier Expression error...

=Num(Round(Count({<TopBoxFLG = {'1'}, QuaterStart(Today()), QuarterEnd(Today())>}SurveyID)/Count({TopBoxFLG = {'1', '0'},QuaterStart(Today()), QuarterEnd(Today())>}SurveyID), .001), '#,##.#%')

Re: Most Recent Quarter Only

lcontezini — Thu, 25 Oct 2018 17:02:51 GMT

take a look at this: Set Analysis Date Range | Qlik Community

You will use QuarterStart(today()) and QuarterEnd(today()) as min and max dates, as in the example.

Your date dimension (the same you use to create "YearMonthAbbreviatedNM") will be the "MasterDate" in the example.

Something like this:

count({< Date_Field = {">=$(=Date(QuarterStart(today()))<=$(=Date(QuarterEnd(today()))"} >} SurveyID)