I'm stuck on a pivot table calculation. I'm not sure if set analysis, if statement, aggr, or what to use on this one.
Pivot Table needs to be:
MonthYear Store Count Jan-2010 102 Feb-2010 110 Mar-2010 127
Table 1 - The traditional calendar table with MonthYear
Table 2 - Fact table that links to the calendar with date and to the store dimension table with store id
Table 3 - Store dimension table with a store counter, open date, and close date
I want to count the stores for a given monthyear period within the open and close date or with null open/close dates. The trick is I want to ignore the fact table. Whenever I try it with set analysis or an if statement I get the count of stores with the open/close date within the monthyear but I'm getting only stores with a record in the fact table.
I can also get a min and max date with all the selected monthyear in variables and use that with TOTAL to get all stores within that period but I need it broken out by each monthyear.
This is the crazy set analysis I'm trying that doesn't work:
= sum( {<StoreKey=, Date=, MonthYear=, OpenDate= {"<=$(=num(monthend(MonthYear), '#,##0')) OR Null"}, CloseDate= {">=$(=num(monthstart(MonthYear), '#,##0')) OR Null"} >} StoreCounter)
Your basic logic looks correct; I see 2 possible problems with the expression:
For this type of condition (where the search expression starts with <=), the value must be the same format as OpenDate, which is probably not a formatted number. My understanding is that set analysis does the comparison in text, not number, form.
I suspect that the OR Null is not valid in that context.
You could try making a full search statement instead, something like (assumes one MonthYear is selected)
= sum( {<StoreKey=, Date=, MonthYear=, OpenDate= {"=OpenDate<=$(=monthend(MonthYear)) OR IsNull(OpenDate)"}, CloseDate= {"=CloseDate>=$(=monthstart(MonthYear)) OR IsNull(CloseDate)"} >} StoreCounter)
I've not tried OR like that, but if the statement works in the advanced search box, it should work in set analysis.
Another thing to bear in mind is that set analysis is performed on the entire data set, it does not respect chart dimensions, so if the MonthYear is coming from chart dimensions, you need to sum a sum(if...) statement instead.
Something like: (have not checked for proper bracket closing)
sum( {<StoreKey=, Date=, MonthYear=} if((OpenDate <= MonthEnd(MonthYear) OR IsNull(OpenDate)) AND (CloseDate >= MonthStart(MonthYear) OR IsNull(CloseDate)), StoreCounter))
Hope that helps
Jonathan
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Try using Interval Match at the back end. It is a lot more easier to do if you do it this way. Please find the syntax and example for Interval Match in the Help document for QlikView.
