topic Expression help to compare 2 charts in QlikView

Expression help to compare 2 charts

Wed, 20 Jul 2011 21:09:58 GMT

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 ($)])

Re: Expression help to compare 2 charts

swuehl — Wed, 20 Jul 2011 21:27:53 GMT

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

Expression help to compare 2 charts

Wed, 20 Jul 2011 21:46:54 GMT

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!

Expression help to compare 2 charts

swuehl — Wed, 20 Jul 2011 22:01:06 GMT

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

Expression help to compare 2 charts

Wed, 20 Jul 2011 22:10:22 GMT

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 😆

Expression help to compare 2 charts

swuehl — Wed, 20 Jul 2011 22:19:56 GMT

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

Expression help to compare 2 charts

Wed, 20 Jul 2011 23:05:35 GMT

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?

Expression help to compare 2 charts

swuehl — Wed, 20 Jul 2011 23:27:20 GMT

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

Re: Expression help to compare 2 charts

Thu, 21 Jul 2011 00:13:23 GMT

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!

Re: Expression help to compare 2 charts

swuehl — Thu, 21 Jul 2011 00:38:41 GMT

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

Re: Expression help to compare 2 charts

rahulgupta — Thu, 21 Jul 2011 10:12:07 GMT

Hey Hi,

Here is your solution

Regards

Re: Expression help to compare 2 charts

Thu, 21 Jul 2011 15:26:59 GMT

Hi Stefan, Thank you!!! This is really helpful! I think I figured out why the title wants to display Q4 for year 2012 even tho there's no data - it's just taking the MAX of the Quarter field - is there any way for it to know to take what is actually being displayed?

Re: Expression help to compare 2 charts

swuehl — Thu, 21 Jul 2011 16:06:12 GMT

Hi,

please look at your data source, you have rows there with FY 2012, and Quarter 3 and 4, but then "Fiscal Quarter and Year" is "2011 Q3" resp. "2011 Q4". In other words, FY, Quarter and "Fiscal Quarter and Year" are inconsistent already in the source data.

That is not correct, maybe you can fix it in the excel file.

I think this will also fix the apparently problem with your titles.

Regards,

Stefan

Re: Expression help to compare 2 charts

Thu, 21 Jul 2011 16:25:41 GMT

Hi, I actually did fix the source data but the problem is still there – I’ve attached the source data to illustrate.

Re: Expression help to compare 2 charts

swuehl — Thu, 21 Jul 2011 16:52:36 GMT

Ok, I see what your problem is.

Instead of max FY and max Quarter separately, we can look at max "Financial Quarter and Year" using maxstring() function.

Regards,

Stefan