Sorting in ascendng order when Variable with logic is a dimension

[mhapanka]

Hi ,

I have a kind of confusing question here!

If you look at the graph below, it uses Variables to to be filtered by which is a dimension as follows:

=if($(vselector2)=1,[Fiscal Year],

if($(vselector2)=2,[Fiscal Quarter],

if($(vselector2)=3,[FiscalMonth1])))

And I have sorted it using Expression: NUM(Replace([Fiscal Quarter],'-Q','')) to get the Quarters sorted right.

However, the months now are not sorted correctly and I'm unsure how to do this because month is not a separate dimension. Quarter, month and year are a part of the variable the graph will get filtered by as given in green above.

How do I get year months and quarters sorted in ascending order when the respective filter is chosen

[petter]

Have you tried using the same If()-logic with the sort expression?

[mhapanka]

=if($(vselector2)=1,[Fiscal Year],

if($(vselector2)=2,NUM(Replace([Fiscal Quarter],'-Q','')),

if($(vselector2)=3,[FiscalMonth1])))

I tried this, didnt work for months

[petter · Accepted solution]

Try

=if($(vselector2)=1,[Fiscal Year],

if($(vselector2)=2,Num#(Replace([Fiscal Quarter],'-Q','')),

if($(vselector2)=3,Num#(Replace([FiscalMonth1],'/','')) )))

BTW - Pick() might be easier to use instead of nesting ifs:

=Pick( vselector2 ,

[Fiscal Year],

Num#(Replace([Fiscal Quarter],'-Q','')),

Num#(Replace([FiscalMonth1],'/',''))

)

[mhapanka]

This worked!

Thanks so much

[petter]

You're welcome