Set Analysis Help

[pkelly]

Hi all...

Dabbling in set analysis for the first time...

What I am trying to say below is...

IF bpf_MonthYear >= varFinancialYear_Last (2009)

AND bpf_Month = 1 (January)

AND bpf_Month <= carCalendarMonth_Last (October)

sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_Month = {"={'1'}<=$(varCalendarMonth_Last)"}>} sin_Sales)

I am utilising the pick(match functionality and I am trying to get a look like the following (will have variances at the end comparing 09 with 10)...

Jan Feb

09 10 09 10

I only want to show completed months in both years so, at the time of writing, don't want to show Nov or December in my chart becuase it will screw up my variances.

Any assistance greatly appreciated.

Regards

Paul

[stephencredmond]

Hi Paul,

Just as a first tip, when using Sets with dollar-expansion, create expressions in a straight table and leave the labels blank (so it defaults to "<Use Expression>"). The expression displayed in the label will be one with the dollar-expansions already calculated. That way, you can see if the expansions have actually worked.

Try that and see if it gets you closer to where you want to go.

Regards,

Stephen

[stephencredmond]

Some general stuff about your requirements and the set.

Month=1 and month < October would appear to be, logically, just Month=1. Should that be an "OR"? (although, logically, that would just be Month < October!)

Your set definition is slightly off because of this. You can include multiple values (for an "OR") within the same set comparison like this:

bpf_Month = {"1","<=$(varCalendarMonth_Last)"}

The thing to remember about sets is that the "=" does not mean "equals"! It means "in this set".

Something like this might work, but I don't have your data to test:

sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_Month = {"1","<=$(varCalendarMonth_Last)"}>} sin_Sales)

One other thing - does your bpf_MonthYear field have a hyphen (-) in it? If so, I have had problems with this in the past and often create a second field without the hyphen to use in set comparisons.

Regards,

Stephen

[pkelly]

Thanks for your response Stephen

bpf_MonthYear is actually just the calendar year e.g. 2009.

I have fiscal year in my table hence the month qualifier.

I am actually looking for an AND rather than an OR.

My current formula is as follows (bpf_Month has changed to bpf_MonthNumber)

pick(match(Column,'January','February','March','April','May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', 'Current Year', 'Last Year', 'Variance Percentage', 'Variance Value')

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'1'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'2'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'3'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'4'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'5'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'6'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'7'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'8'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'9'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'10'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'11'}>} sin_Sales)

, sum({<[bpf_MonthYear] = {">=$(varFinancialYear_Last)"}, bpf_MonthNumber={'12'}>} sin_Sales))

This gives me a table running from January to December for this calendar year and last (still to add formula for additional columns).

The issue I have is that, I only want to show values for this year / last - for completed months this year.

So, at the point of writing, my table would only show January to October figures for 2009 and 2010.

Variance etc would also be on this basis.

I now have a variable called "varCalendarMonthNumber_Last" which stores the last completed month number.

Therefore, I only want to do the calculation for the specific month if it is less that or equal to varCalendarMonthNuUmber_Last.

Hope that makes some sort of sense...

Regards

Paul

[stephencredmond]

Hi Paul,

I think that I see what you need now. You can use a Set intersection to get what you want:

pick(match(Column,'January','February','March','April','May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', 'Current Year', 'Last Year', 'Variance Percentage', 'Variance Value')
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'1'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'2'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'3'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'4'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'5'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'6'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'7'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'8'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'9'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'10'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'11'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales)
, sum({<[bpf_Monthbpf_MonthYear] = {">=$(varFinancialbpf_MonthYear_Last)"}, bpf_MonthNumber={'12'}*{'<=$(=Max({1 <bpf_MonthYear={$(=Max({1} bpf_MonthYear))}>} Month))'}>} sin_Sales))<div>

Regards,

Stephen

[pkelly · Accepted solution]

Thanks Stephen

Have ended up doing this using an if statement - see below..

Will need to spend some more time on set analysis to get my "sore" head around it...

Thanks for all your help...

pick(match(Column,'January','February','March','April','May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', 'Current Year', 'Last Year', 'Variance Percentage', 'Variance Value')
, Sum(IF(bpf_MonthNumber =1 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =2 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =3 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =4 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =5 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =6 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =7 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =8 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =9 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =10 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =11 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
, Sum(IF(bpf_MonthNumber =12 AND bpf_MonthNumber <= varCalendarMonthNumber_Last AND bpf_MonthYear >= varFinancialYear_Last , sin_Sales))
)