Future Quarters in Alternate States based on Quarter Selection

[sunainapawar]

Hello All,

I have a requirement in Quarter Selection in Alternate States. 

I need to compare growth of different Quarters in a pivot table, hence i have used Alternate States for comparision purpose. I have defined 2 states State1 for Quarter1 and State2 for Quarter2 selection.

But the requirement is, Suppose in State1 if user selects previous quarter then in Quarter2 it must show only those quarters which are after previous quarter.

Consider, user selects Q421(Q2021-2022), then in Quarter2 it must only show Q122(Q2021-Q2022) in a filter pane. Can anyone assist me with this.

[sunainapawar]

Hello All,

Any update. Please assist.

Thanks in advance.

[tresesco]

Not really sure what exactly and how you are expecting this to be presented. My initial thought would be - using set analysis could be an easier approach to do this comparative analysis. However, that depends on your requirement a bit. In such questions, the more clarity you can put on requirement with a sample app - the better chances you create to get your answer here. Try to share your sample app and explain the requirement w.r.t that. 

[sunainapawar]

Hello Tresesco,

Thanks for replying. Though i will be unable to create a sample app. I will share the screenshots of the requirement to make it more clear.

In a pivot table, we need to compare the growth for Quarter to Quarter.  My pivot table looks like below.

I have defined 2 alternate states as State1 and State2. In the pivot table, First column is applied as State1 and Second column is aplied as State2.

I have 2 filter panes in which i have added the same Quarter Field. So Quarter values are same. For 1st Filter pane i have applied State1 and for 2nd State2. So when the selection is made in State1, in the table the first column shows the Sum(value) for that Quarter. And when i need to compare with another Quarter i select different Quarter from 2nd pane.

Now in this, if user selects Q321 in State1 , then in State2 it should only show Quarter values Q421 and Q122, because these are greater or after Q321. Basically based on Quarter Selection in State1, in State2 it must show future quarters. 

let me know if any further details required.

[tresesco]

Here is the expression (logic) to be used. I am using  Year field for demo; adjust accordingly for your case.

=Aggr(Only({[State 2]<Year={">$(=Max({[State 1]}Year))"}>} Year), Year)

[sunainapawar]

Hello Tresesco,

Thanks for the response. The solution is perfect. 

In my case, the Quarter format is Q321,Q421. Hence max is not working. I tried using Maxstring. Maxstring(Quarter) shows properly but when i select Qaurter from State1, State2 Quarter is not displaying,

[tresesco]

You are trying to compare >/< with string in set analysis. It doesn't work; it expects numbers for such comparisons. Either create a dual field for quarter in the script or try a bit more complex set analysis, like:

=Aggr(Only({[State 2]<Quarter={"=Right(Quarter,3)>$(=Right(MaxString({[State 1]}Quarter),3))"}>} Quarter), Quarter)

[sunainapawar]

Hello Tresesco,

Thanks for the reply. This solution is working fine for some selections.

But based on Year, it is not showing properly. Suppose, i select Q122 of 2021, then it must only show Q222.

But it is showing last years Quarters as well. I tried using =Max(Year) in the expression but it is not working.

Any suggestions?