Set Analysis - months selected versus prior months

[Anonymous]

I have a field called [Fiscal Period Date] which contains dates representing fiscal months. In my chart I need the first expression sum([Sales Pounds]) for all the months the user selects (no problem) and another expression to sum all the equivalent months 12 months prior to what the user selected. So, if the users selects:

Expression 1 (obviously)

=sum([Sales Pounds])

Expression 2 (doesn't work)

=sum({$<[Fiscal Period Date]={'>=$(=AddMonths(Min([Fiscal Period Date]),-12))<=$(=AddMonths(Max([Fiscal Period Date]),-12))'}>}[Sales Pounds])

Yields this result:

The second expression should give me 2,145, the sum of months 09/01/2012, 10/01/2012 and 11/01/2012. I suspect I may be over-complicating it.

[swuehl · Accepted solution]

Brian,

if you you remove the label from the second expression, then hover with the mouse over the expression label cell, QV will show the expression definition, with the dollar sign expansions expanded.

What do you see? Has QV evaluated the dollar sign expansions correctly, with a correct date format matching your field format?

Another issue may arise if the user actually doesn't select in field [Fiscal Period Date], but in some other calendar field.

This could lead to an incompatible set with your set expression overriding [Fiscal Period Date]. I assume you have tested it with only selecting in this field, so this is probably not the issue. But to overcome this issue, you would need to disregard user selections in other calendar fields:

=sum({$<[Fiscal Period Date]={'>=$(=AddMonths(Min([Fiscal Period Date]),-12))<=$(=AddMonths(Max([Fiscal Period Date]),-12))'}, Date=, Month= , YearMonth= , Year= >}[Sales Pounds])

[Anonymous]

It was a difference in date formatting. The trick about removing the label in order to view the evaluated expression, which I can't believe I never noticed, helped me spot the problem. Thanks for the help!

[#4]

Hi Swuehl,

When I'm face to this problem, I'll try to avoid it. Take Year&Fiscal Year for example, I think these two dimensions are incompatible, and it would be weird if the current selection shows these two at the same time.

If these issues happens on different sheets, I'll choose sheet triggers to clear the incompatible ones, and it's also feasible to keep the cleared values if necessary. For example, Sheet1.[Sales Year] and Sheet2.[Cost Year].

If these issues happens on the same sheet, like [Year] and [Fiscal Year], using variable triggers to control would resolve it.

[ali_hijazi]

in your set analysis and since you are working on the default set of data ($)

you need first to ignore selections made on Fiscal Period Date

{<[Fiscal Period Date]=,<[Fiscal Period Date]={'>=$(=AddMonths(Min([Fiscal Period Date]),-12))<=$(=AddMonths(Max([Fiscal Period Date]),-12))'},