comparing data selected year to year previous to selected, selected month to month previous to selected,selected quarter previous to quarter previous to selected

[#1]

Hi there,

i have a master calendar  to use to compare values through a period of time.

I have 3 listboxes

The first contains the years: 2010,2011,2012

The second contains the Quarters with the corresponding year:

Q1-2010, Q2-2010,Q3-2010,....,Q3-2012

The third contains the Months with their corresponding Years:

Jan-2010, Feb-2010, Mar-2010,.....,Jul-2012

I have a table in which i want to display the count of a transactions database and the sum of these transaction value, according to the selected listbox, of the selected value versus the one in the previous year,eg if Q1-2012 is selected i want to compare it with Q1-2011 or if Mar-2012 is selected i want to compare it with Mar-2011, and the previous one, eg  if Q1-2012 is selected i want to compare it with Q4-2011 or if Mar-2012 is selected i want to compare it with Feb-2012.

Only one selection at a time should be permitted form one listbox.

The user must not be able to select  eg Q1-2010 And Jan-2010 . Only one of them.

Also i would like to incorporate an if statement that will show the desirable result according to the selected option.

Thanks in advance for any answers.

[robert99]

I did something similar using the formula in the manual under set analysis (adjusted as appropriate)

sum({$<Year = {$(#=only(year)-1)}>}Sales)

But I did not use a list box or quarters. Just select fields where the month (s) and year was selected as appropriate.

This meant that any combination of months could be selected (including quarters) as required.

[Anonymous]

The key points are in bold:

Selected year:     sum({$<Quarter=, Month=>} Amount)
Selected quarter:     sum({$<Year=, Month=>} Amount)
Selected month:     sum({$<Quarter=, Year=>} Amount)
Year before selected:     sum({$<Year=, Quarter=, Month=>} if(InYear(Date, '$(=makedate(Year))', -1),Amount))
Quarter one year before selected:     sum({$<Year=, Quarter=, Month=>} if(InQuarter(Date, <any date in the selected quarter>, -4),Amount))
Month one year before selected:     sum({$<Year=, Quarter=, Month=>} if(InMonth(Date, <any date in the selected month>, -12),Amount))

I can't tell for sure how you define "any date in the selected quarter or month" because it depends very much on how it is formatted in your application. I hope this information is enough for you to figure out the rest.

Regards,
Michael

[Anonymous]

hello everybody; i'm a newbie and i've a similar problem: compare sales "by year" (read select a month and compare with the same month of the previous year) first and then compare sales "by month" (read select a month and compare with the previous month of the same year).

• first comparison (year) - sum({$<Year = {$(#=Only(Year)-1)}>} Sales ), from users' manual; i got it, i don't understand all about this expression but it works for my pourpose!
• second comparison (month) - sum({$<Year = {$(#=Only(Year))}, Month = {$(#=Only(Month)-1)}>} Sales ) by me... and it only shows zero!!! no result!

what's wrong with my expression!!! btw, i set month and year in script loading by adding Year(Date) as YEAR as well as month... many thanx...