Limit number of months in graph

[#1]

Hi all,

I have a problem with Set Analysis that I can't seem to fix. I have a field called 'Source' which has a variety of values in it, one of which is a set of numbers, such as 2+10, 5+7, 12+0. They always add up to 12 as they show the number of complete months + number of estimated months (e.g. 2+10 = 2 complete months + 10 estimated months). In each reload the values are the same (e.g. all the values are 5+7 for this 'Source' at the end of May) but there are other sources, e.g. 'In Market' and 'AOP'.

Each row has an associated value against it. I wish to graph the values by source by month (e.g. AOP, 5+7) but where the source is a set of numbers I only want to graph the first n months of values (the complete months), where n = the first number in the source row (e.g. 5 in this case, so Jan - May) and not display the estimated months (in this case the last 7 months). The name is 5+7 but the months are displayed as Jan, Feb, Mar etc so I'm not sure how to convert the numbers into months in set analysis.

The 'Source' number will change each month (e.g. 1+11 to 2+10 to 3+9 etc) so I need this to be dynamic and change each month automatically. I've done a set analysis to ensure that even if the field name changes (as explained) the graph will display the values so I need it to incorporate this too.

I've attached an example to help.

Many thanks in advance!

[rubenmarin]

Hi Kevin, not sure to understand your requirements and how it will be the complete set of date, with the sample you provided i made this, check if this is what you are looking for.

I set the limit in the 'Dimension Limit' tab of the graph.

[avinashelite]

Hi Kevin,

In script use this expression:

On the Source field try like this

1.to get only first numbers with out text from source field: Num(SubField(Source,'+',1)) as source_without_text

2.to get only first numbers with text from source field: SubField(Source,'+',1) as source_with_text

Since i was not able to reload the data  i am attaching the eg

[#4]

Hi Ruben,

Sorry for not being clear. You are pretty much there, the only thing I need to clear up is that I need the 'AOP Sales by Month' to show full 12 months but only 5 months (in this case as it is 5+7) for 'LBE Sales by Month'.

Does that make sense?

Cheers

Kev

[#5]

Hi Avinash,

Thanks for your reply. I'm not sure how to use this? In your Example file I would like to show all 12 bars for the blue 'AOP' values but only 5 (in this case) for the red 'LBE' bars.

Hope that makes sense?

Thanks!

[rubenmarin]

Hi Kevin, then I'll limit that on the expression itself, and using subfield as Avinash said is better.

If you load month as a real month (not as a text like in the sample) you can use

If(Month<=Subfield(MinString(Source), '+', 1)

[#7]

Hi Ruben,

I've tried to do this but it doesn't show anything in the graph? The formula I've used is shown below. I've extended the Source exclusion to account for the actual data in my QVW:

=

If(Month<=Subfield(MinString(Source), '+', 1),
sum({<Source

=- {'AOP-Ex Factory','AOP-In Market','In Market','Latest QV'}>} Sales)

As the first part of the formula ends in ",1)" will this formula still work where the first part of the Source field is (e.g.) 10+2? Is it just taking the first digit and not the second (i.e. "1" instead of "10")?

The script below shows how I am getting the month which I think should make this work (as I think this makes it a 'real' month)? TypeDate is just a field that contains text plus the month and year.

  Month(date(date#(right(TypeDate,6),'MMM-YY'))) as Month,

Cheers

Kev

[rubenmarin]

Hi Kevin, SubString funtion will return the string before the '+', so from the string "10+2" it will return "10", to avoid sorting problems (not sure if 10+2 will be before 2+10) maybe is better using this:

=If(RowNo()<=Min(Aggr(Subfield(Source, '+', 1), Source, Month)),

sum({<Source =- {'In Market','AOP-Ex Factory'}>}Sales))

Probably is only a typo but just in case...in the expression you posted you need to close the 'if' adding ')' at the end