Problem with a bar chart (number of items active for a period of time)

[nachofernandez]

Hi,

I´m trying to create a bar chart  that with a start and end date selected, it tells me the number of products that are active in each selected date.

The original table could be:

Product	Start Date	End Date
prod1	01/01/2011	01/05/2014
prod2	01/01/2012
prod3	01/01/2013	09/31/2013
prod4	10/01/2013

I´ve created the fields StartDateYearMonth and EndDateYearMonth

Besides, i´ve created an island calendar table, where there are all year-months since 01/01/2011, with a field called CalendarYearMonth.

I´ve created a bar chart with dimension StartDateYearMonth and this expression:

count( {<StartDateYearMonth ={"<=$(=Min(CalendarYearMonth))"}>*(<EndDateYearMonth={">$(=Max(CalendarYearMonth))"}>+<ProductActive={1}>)} DISTINCT product_id)

ProductActive is a calculated field when the end date is null.

If i select two CalendarYearMonth  i want that my chart shows me for each month between these two dates the number of products active. However, the expression above gives me the number of products created in each month.

The final goal is to achieve that if i select CalendarYearMonth these two dates (201101 and 201310) the idea is (in brackets number of products):

201101(1)...201102(1).......201112(1)........201201(2)........201301(3).....201310(2)

Is there a way to achieve this?

Thanks in advance.

Best Regards.

[marcus_sommer]

The expression looks somehow strange. ProductActive is a field or a calulated dimension? Afaik you couldn't use calculated dimensions as field within set analysis, only real fields. I would try:

count( {<StartDateYearMonth ={">=$(=Min(CalendarYearMonth))"},

              EndDateYearMonth={"<=$(=Max(CalendarYearMonth))"},

              ProductActive={1}>} DISTINCT product_id)

- Marcus

[nachofernandez]

Thanks Marcus,

ProductiveActive is a real field (calculated in script).

With that formula you only have the products that  are active. I want the products active in the selected date. The part of the expression that controls that is:

*(<EndDateYearMonth={">$(=Max(CalendarYearMonth))"}>+<ProductActive={1}>)

I mean, or the end date is higher than date selected or the end date is null (product active).

Going further on the problem i´ve changed the actual dimension to CalendarYearMonth. This has gven me the correct result....but only for one date. When i pick a range of dates, the result is a chart with all bars with the same number of products.

I´m still working on it then.

[swuehl]

Have you thought about using an INTERVALMATCH?

You would need to create  value for EndDate (like 12/31/2999 ), at least for the INTERVALMATCH (i,e, you can create an addtional field for the matching).

There are lots of samples here in the forum with a quite similar requirement.

Using the INTERVALMATCH, you connect your calendar to the date range records, no need for set analysis anymore.

[marcus_sommer]

Ok. now I understand your expression better and I think it's rather not a syntax issue it's more a logical problem of these complex expression or the data-model worked as expected.

I would try to split these expressions into parts and using fixed values for testing reasons to see if they return the expected results. Further I would thinking if I could simplify the expression by doing to replace NULL in EndDateYearMonth with a value from 12/31/9999 maybe in an additional field or something similar.

- Marcus

[swuehl · Accepted solution]

Here is an example using Intervalmatch.

[nachofernandez]

Hi,

I´ve achieved what i wanted, but with a more agressive solution. I´ve created a script that stores for each month, the product_ids that where active in that month, I know that´s not the best solution, but i needed to solve it quickly.

I´ve changed the focus before reading these answers. I think that the Interval Match issue could work. I will try it to see if i can delete my workaround (but now, with less hurry hehe)

Thank you all.