Quarter sorting and filter

[amruta_j]

Hi All,

PFA the screenshot. 

1. I need to order the quarter like Q1-2019, Q1-2018,Q2-2018,Q2-2017,Q3-2018,Q3-2017,Q4-2018,Q4-2017. Please help me to sort Quarter field like mentioned.

2. Now Only Q1 is available for 2019, so I need to show Q1 comparison for 2019 vs 2018, but for other quarters I need to show comparison between 2018 vs 2017 quarters.

Thank You!

[anushree1]

You can do it via Expression in Sort Tab

Expression =Match(Quarter_Field_Name, 'Q1-2019',' Q1-2018','Q2-2018','Q2-2017','Q3-2018','Q3-2017','Q4-2018','Q4-2017')

if Not you can also create an Inline Table with 2 columns  Quarter_Field_Name and Sortweight and sort based on Sortweight  Field by passing the same as expression in the Sort Tab

[daanciorea]

Hi,

For your first issue you can calculate another field, at the same time that you calculate the field Quarter-Year, with this logic: 20191, 20184, 20183.... and then you can short by this field.

For your second issue, Can you share the expresion that you use?

Dan.

[amruta_j]

For first point, I'm calculating Quarter Year in model like 

'Q' & ceil(month / 3)&'-'&year AS Quarter, Not sure how to sort it without hardcoding the year.

For second point, my expression is just sum(Total_Leads), but I'm creating cyclic expression for QuarterYear and MonthYear like below:

QuarterYear (sorted State and Text)

MonthYear (sorted with Expression 

wildmatch(MonthYear,'Jan*','Feb*','Mar*','Apr*','May*','Jun*','Jul*','Aug*','Sep*','Oct*','Nov*','Dec*') )

So I'm guessing I need to use set expression for 2nd point, but not sure how.

[daanciorea]

For the first point , add the folow field,
year & ceil(month / 3) AS QuarterShort.

For your second issue, test to use :
1. Create new field ---> ceil(month / 3) AS QuarterTotal
2. Create a new variable: vMaxQuarter --> max(QuarterTotal)
3. Use this expresion ---> Sum({<QuarterShort = {$(=$(vMaxYear) & $(vMAxQuarter))}>}Total_Leads) / Sum({<QuarterShort = {$(vMaxPreviousYear) & $(vMAxQuarter)}>}Total_Leads)

I hope it's work.

Dan.