Set Analysis - evaluating dates using variables

[#1]

Hi,

I would like to create a set analysis, that compares dates outside of the current selection, that are greater than the "MinPeriod" and less than/equal to "MaxPeriod" (where both "MinPeriod" and "MaxPeriod" are variables).

Please see attached QV doc...

Anyone have any thoughts on the matter?

Kind regards,

Rich

[#2]

thought something similar to the below would work, but I can't make it work

sum({CalYearMonth = {>=$(zzMinPeriod)<=$(zzMaxPeriod)}>} Euros)

Any thoughts?

[Miguel_Angel_Baeyens]

Hi Rich,

Check this application using variables in ranges and set analysis. Note that the format in CalYearMonth must be exactly the same than the returned by zzMinPeriod and zzMaxPeriod. In regards to use Date fields instead of string periods to get dates right, check this thread.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Hi Miguel,

Many thanks for your response!

In your reply you identified two further areas for me to investigate: 

1) check that the formatting is consistant between the time periods in my data, vs the time periods being returned in my variables

2) review the script used in a separate application, which tackles the same objective

1) In the revised attachment, I have created some test text boxes to evaluate (when only 1 "CalYearMonth" period is selected), whether the selected month meets a number of criteria based on the variables used (ie does "CalYearMonth" match the max selected date (variable = "zzMaxPeriodThisYear")  , whether "CalYearMonth" is greater than the min date of last year (variable = "zzMinPeriod"), and whether "CalYearMonth" is less than the max date from the previous year (variable = "zzMaxPeriod")

The results of the above test return as expected.  Due to this, is it safe to assume that the formatting matches both in my data, and in the created variables?

2) Looking at the document posted in one of your previous threads, I have identified the following formula to meet my needs:

" Sum({<CalendarDate = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'}>}SalesAmount) "

As a result of the test performed in part 1) above, have decided (correctly or incorrectly?) to rem(ove the "Date()" function in the above syntax (as I believe the 2 are in the same format).

Tailoring the above to my data, I have generated the following syntax:

" Sum({<CalYearMonth = {'>=$(zzMinPeriod) <=$(zzMaxPeriod)'}>}Euros) "

which doesn't return the expected results...

Does anyone have any additional thoughts?

Please advise,

Kind regards,

Rich

[#5]

Could someone kindly cast their eye over the above issue please?

Most appreciated,

Kind regards,

Rich

[#6]

Hi Rich,

Attached is your file that will hopefully get you some way to the solution.

There was a problem with your syntax, you need to add an AND to your set analysis.

The other issue, as I see it, is that your dates are strings and as such greater than/ less than cannot evaluate. You can use the dual function, or I think the date function to make sure a numerical value is associated to your variables and month/ year fields.

Hope this helps

James

[Miguel_Angel_Baeyens]

Hi Rich,

Check the attached (and ammended) application. First, I have created a CalDate field that stores the actual date for a given period. In your case, is done based on the CalYear and CalMth, and will return a date like 01/02/2011 (first day for the month and year given as parameters).

Second, I've modified the set analysis expression, so it takes into account the dates between the two periods (making some string tweaking that may slower your chart if you have a lot of data), regardless the selections in the fields that populate the variables.

Actually, I'd rather do the opposite: create a variable to store what the user see (FY08/09, FY10/10, etc) and use this in the set analysis, leaving the date fields. The reason is that when you select in a field, that selection affects the set analysis as well, unless you specify otherwise, in your application leaving the right part of the CalYearMonth and FiscalYear blank (it's not a syntax error). And if your selection is excluded from the set analysis (say set analysis only selects 2011 and you select 2010 in the Year field) the expression and the chart will return unexpected results.

Hope that the application attached as well as my previous application help.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#8]

Hi Miguel,

Many thanks for the attached!

Having reviewed your file, im unsure whether its taking into account the dates being returned in the variables correctly. 

ie when only the "CalYear" has been set to 2009, the variables return the following dates for the previous year:

zzMinPeriod = 2008/01 

zzMaxPeriod = 2008/12

Although the results being displayed in the revised table, are only dispalying the selected spend (in this case 2009, and not the data between "2008/01 to 2008/12"

Do you have any idea how to resolve?

Kind regards,

Rich

[#9 · Accepted solution]

Hi James,

Many thanks for your review!

Have played with the file and with a few tweaks got what I wanted, so many thanks!

Key change that I made, was in the set analysis to remove the current period selections (in the CalYear, FiscalYear and CalYearMonth fields)

Other things I tailored were to take account of incomplete years (ie 2011 in the attached, where only 3months data included).  Have updated the attached to show a YTD comparison, and then taking into account not a full year to return complete previous year.

Once again, many thanks for your input

Kind regards,

Rich

[#10]

Thanks both