Set Analysis - addmonths

[#1]

Hey Guys,

What I'm trying to do is count for the same month in a table for last year. For example, if Jan-2010 and Feb 2010 are selected the table will display like

MonthYear	This Year	Last Year
Jan-2010	120	95
Feb-2010	200	100

I'm having troubles getting this expression to work

=count(total distinct {$< MonthYear={"=date(addmonths(MonthYear,-12),'MMM-YYYY')"} >} Counter)

If I use the expression below it will work for Jan. So, I'm not sure why the above one isn't working.

=count(total distinct {$<MonthYear={"Jan-2009"}>} %[Buying_Store_Counter])

I also tried setting a variable vDateLY to

=date(addmonths(MonthYear,-12),'MMM-YYYY')

And then set the expression to

=count(total distinct {$< MonthYear={'$(=vDateLY)'} >} %[Buying_Store_Counter])

That works but only when one month is selected.

[#2]

You probably need a dollar sign expansion to get the AddMonths function to work. Something like:

=count(total distinct {$< MonthYear=
{"$(=date(addmonths(MonthYear,-12),'MMM-YYYY'))"} >} Counter)

Put that expression into a chart without a label. When the chart is rendered, the label will be your expression with the dollar sign expansion evaluated. Dates are tough, because formatting can be an issue. This will tell you the result of your function and you should compare it to your version with the hardcoded date.

I still don't know if that's going to get you the result you're looking for, because it will not be based on the dimension. The dollar sign expansion is evaluated once for the entire chart, not for every record. EDIT: Here is a previous discussion that talks about a similar issue and offers some solutions: http://community.qlik.com/forums/t/23889.aspx

[#3]

That expression did work when one monthyear is selected but your right it still isn't working for mulitiple months.

I tried adding a new expression LY to the chart and it is showing the same month last year.

=date(addmonths(MonthYear,-12),'MMM-YYYY')

Then changed the counter to

=count(total distinct {$< MonthYear={"[LY]"} >} Counter)

But that isn't working. Do you know if it is possible to add an expression to set analysis in another expression.

[#4]

That won't work, because the Set Modifier part won't be evaluated record by record. Is the date one of your dimensions in the chart?

I've found the easiest way (for me) to handle this is by including some of this information in the load. In one application, we load a separate set of fact fields for the previous year. For each record, we have Sales and PY Sales, that way, we just add each field to the table.

You could also use an if...then:

=count(total distinct If(MonthYear=date(addmonths(MonthYear,-12),'MMM-YYYY'), Counter))

Are your users selecting a year to display on the chart? Set Analysis works great with selections or across the entire data set, but not on a record by record level. If the user is making a selection of the current year, then you can do something like:

=count(total distinct {1<Year = {$(=Max(Year)-1)}>} Counter)

Or, if the current year is always the current year (Current Year is the Max(Year) in your data set), then the above expression should work without the 1.

[#5]

Yeh, you are right. I'm going to have to load a separte set.

I hate to bug you more but can you go into more details on how you did that.

My fact table is

FactTable:
Load
FactKey,
SaleDate,
Product,
Customer,
Price,
Counter
From sales.qvd (qvd);

[#6 · Accepted solution]

Something like this may work:

FactTable:
Load
 FactKey,
 SaleDate,
 Product,
 Customer,
 Price,
 Counter
From sales.qvd (qvd);
Join Load
 AddMonths(SaleDate, 12) As SaleDate,
 Product,
 Customer,
 Price As PY_Price,
 Counter As PY_Counter
From sales.qvd (qvd);

That should load all your current data is normal. The second part adds one year to the sales date (so the previous year values get the current year SaleDate). The two tables will be joined on SaleDate, Customer and Product. I don't know if that is exactly what you need, but that's the basic idea.

[#7]

You are awesome. That worked like a champ.