Expression help to compare 2 charts

[#1]

Hi, I have 2 charts side by side of the top 10 products.  I'd like the first chart to display the current quarter which would be the max of the Fiscal Year and Quarter and then next to it another chart that would show a year back and let you use filters to flip thru the quarters. Does anyone know how to do this?

Thanks!   Chart 1 my expressions shows:

Chart 1 - Sum({$<[Fiscal Year]={$(#=Only([Fiscal Year]))}>}[TOTAL REVENUE ($)])
' Is there a way to only pull MAX Fiscal year and MAX Quarter?
Chart 2 - Sum({$<[Fiscal Year]={$(#=Only([Fiscal Year])-1)}>}[TOTAL REVENUE ($)])

[swuehl]

Hi,

have you tried to use max() function instead only and to also set quarter in set expression?

Maybe like:

Sum({$<[Fiscal Year]={$(=max([Fiscal Year]))},[Quarter]= {$(=max([Quarter]))} >}[TOTAL REVENUE ($)])

if Quarter is of numeric or date type.

Please note that if you have also a Date and or month selected, you might need to clear the Date selection, like [Date]= , [Month]=

Regards,

Stefan

Nachricht geändert durch swuehl

[#3]

Hi, Thank you - Changed the 1st chart to use your max expression function, however I'm still unsure how to have the 2nd chart compare a year ago and then flip thru the quarters?  Any ideas?

Also, is it possible to show the what the fiscal year and quarter represent in the chart in the title somehow?

thanks again!

[swuehl]

What do you mean with flipping thru quarters? May be with a variable selection box?

Sum({$<[Fiscal Year]={$(=max([Fiscal Year])-1), [Quarter]={$(varQuarterSelection)} }>}[TOTAL REVENUE ($)])

A charts title is also an expression, so you could write something like

= 'Revenue for FY' & max([Fiscal Year]) & ' Q' & max([Quarter])

to get a title like

Revenue for FY2011 Q3

Regards,

Stefan

[#5]

Thanks! I'm still struggling a little. 

I'm not sure how to create a variable selection box?  I went to settings/variable overview/add

Also, when I used the expression in the title it still chose Q4 for FY2012 even tho it hasn't happened and doesn't have any data?  It should have shown Q2

Thanks again for your help 😆

[swuehl]

Hi,

variable selection is done using an input box which is a sheet object like a chart (use context on sheet).

There you may define a new variable (like varQuarterSelection ) or choose existing one.

You can fill preset values with a dimension like Quarter using the expression field provided in the object.

If you see Q4 2012 but don't have data than you probably use a master calendar, do you?

The text should change as soon as you select a value (e.g. in list box).

You probably have a date like order date? You might limit the available date for selection by e.g. limiting the master calendar to the max order date.

Hope this helps,

Stefan

[#7]

I'm not sure I understand - I created a input box and created buttons assigning values but my chart is still not populating? I'm not using any kind of a master calendar either?

[swuehl]

Hi,

that's getting a bit difficult to analyze from remote. If you want you could post your sample Application (there is an upload option in advanced editor here in the forum).

Have fun,

Stefan

[#9]

Hi, hopefully this will help. I created an example file that shows my difficulties with the titles matching what it's actually pulling and that it won't show the previous years and qtr. 

Thanks again!

[swuehl]

Hi,

you were pretty close.

I fixed a typo (missing bracket, that's sometimes hard to find in the expression editor), tweak a little bit the input box and corrected the chart title to show variable instead of max Quarter.

There is something strange in your data set, if I select FY2012, it shows also available for selection FY2011 Q3 and FY2011 Q4, but FY2011 and FY2010 seems to be correct. It also seems to me that quarters FY2012 Q3 and FY2012 Q4 should exist, because there is a linkage to these quarters.

Might be an idea to check your source data.

I go to bed now, have a nice day / evening,

Stefan