Show comparison of different years,quarters data on a bar chart

[#1]

Hi All,

I have to show the comparison of different years or quarters on a single bar chart. So that suggests I need two expressions, so I have 2 bars for every month(dimension on X-axis), but whats the best way to do these comparisons, using '$1' or set analysis?

The comparison being like:

2010 Q1 Vs 2009 Q1,

2010 Vs 2008,

2008 Q2 Vs 2006 Q1, or any such combinations of Year,Quarter,Months,WeekDays.

I tried using :

1. sum({$1}Field)

2. sum(${<Year={$(variable)},Quarter={$(variable)}>}Field) [Here variable is the actual variable that holds the previous year selected by user]

But the results vary every time, and seem to work only for year comparisons. Am I going wrong in writing these expressions? Whats the difference in using either of these?

Any suggestions???

Thanks.

[#2]

Your first expression is off. {1} means ignore selection; {$} means respect selection (the default). I don't think you want both.

For the second one, if the user is selecting a year and your variable is set to the previous year (or something else based on the selection), then you probably want 1 not $ and that symbol goes inside the brackets. Like so:

sum({1<Year={$(=variable)},Quarter={$(=variable)}>}Field)

Does your variable hold a date, a year or a quarter? You won't want to directly use the same variable for both Set Modifiers.

There are going to be different ways to approach all of these graphs, it all depends on your data and what kind of selections will be made. It may be easier to give exact expressions if you provide some sample data.

[#3]

Thank you so much for your reply Miller, that explanation surely helps out and gives a clear understanding.

Well, I have variables set for previous(previous being the previous selections made by the user in the year,quarter,month,weekday listboxes) Year,Quarter,Month,Weekday respectively. And using these variables I need to compare the current selections made against the previous selections. And these may be any combinations like, current selected year to previous selected year, quarter of current selected year to similar quarter in previous selected year,a day of particular month of a year to same day in some other year etc..

The comparisons being done on basis of selections from the Year,Quarter,Month,WeekDay listboxes on the sheet.

If I use the below expressions for comparisons between current and previous selections would I be right?

Current selection : sum( Field )

Previous selection : sum({1<Year={$(=previousselectedyear)},Quarter={$(=previousselectedquarter)},Month={$(=previousselectedmonth)},Weekday={$(=previousselectedweekday)}>} Field)

But my concern is, what if the user just selects year and quarter for selection, in that case the previous month and weekday variables values are NULL [have set default value as NULL]. So will that affect the results of the second expression mentioned above? This being true for any combinations mentioned above.

And all of these comparisons need to be shown on the same bar chart, so incase I use these two expressions will it be fine? Sorry about not posting sample data, but there's lots of data and tables used so not able to figure out what data should be provided as an example, and so have come up with this query description.

Thanks again. Appreciate your help.

[qliksus]

Hi i have created a key in the back-end with the combination year&quater&month

and then i have created 2 variables for month,quater where the variable

mon =IF(ISNULL(MONTH),'',MONTH-1)

quat=if(isnull(QUARTER),'',QUARTER-1)

after this i have used the below expression

SUM({<key={"$(#=left(YEAR,4)-1)$(qaut)$(mo)*"},QUARTER=,MONTH=>}[Service QofE %])

and for the selection in the listbox i have used "left(key,4)" as the year list box

i hope this is working as per ur requirement if not let me know i have attached the file for ur reference

[#5]

Hi,

Thanks for your reply. Seems to be a good option and will try it out. For now, I have taken up an alternative way for this query, it seems to be working fine.

Thanks.

[#6]

Hi Lawrencem,

I am also facing the same situation as you had discussed in this thread, can you please share the final code or the steps that you had used to implement it.

Regards,

Kingshuk