Set Analysis using variable for yearmonth expression error

[#1]

Hi,

Help would be appreciated on the following.

I need to display quantities per month and also what was the quantities 2 months back in a pivot where yearmonth is my dimension.

I got it right to create a variable that contains the yearmonth as it was 2 months back but when i want to use this variable in a set analysis to display what was the quantities 2 months back it is not working ? ?

vMakeDate = AddMonths( (MakeDate( left(YearMonth,4),right(YearMonth,2))) ,-2) (this variable gives correct result)

'vCompareDate' = ($(vMakeDate),4) & mid( $(vMakeDate),6,2) (this variable gives correct result)

this is the expression that is not working:

sum( {$< FORMFLAG = {'A'},YearMonth = {$('vCompareDate')} >} Quantity ) - THIS IS MY SET ANALYSIS IN THE EXPRESION THAT IS NOT WORKING

below is the result - yellow is my yearmonth dimension and the blue is the new yearmonth - 2 months that is correct , but when is use this blue value is my vCompareDate variable . The brown is the 0 that my expression return - it seems that it 's confused about vCompareDate OR im missing something somewhere.

YEAR MONTH	200911	200911	200911	200912	200912	200912
=AddMonths( (MakeDate( left(YearMonth,4),right(YearMonth,2))) ,-2)	2009/09/01	2009/09/01	2009/09/01	2009/10/01	2009/10/01	2009/10/01
=left(AddMonths( (MakeDate( left(YearMonth,4),right(YearMonth,2))) ,-2),4) & mid( AddMonths( (MakeDate( left(YearMonth,4),right(YearMonth,2))) ,-2),6,2 )	200909	200909	200909	200910	200910	200910
FOCUS BRAND	Forecast Order Quantity	Quantity	PREV QUAN	Forecast Order Quantity	Quantity	PREV QUAN
	230,629	129,183	0	144,506	83,321	0
Total	47,075	25,088	0	3,981	2,181	0
Product1	86	0	0	13	1	0
Product1	79	0	0	2	0	0
Product2	146	146	0	99	99	0
Product3	72	72	0	1	0	0
Product4	908	484	0	100	80	0
Product5	617	610	0	17	17	0
Product6	151	0	0	2	0	0
Product7	42	0	0	1	0	0
Product8	717	610	0	15	12	0
Product9	995	754	0	109	96	0
Product10	57	0	0	1	0	0
Product11	44	0	0	2	0	0
Product12	215	74	0	17	6	0
Product13	1,188	902	0	82	26	0
Product14	2,190	1,832	0	70	60	0
Product15	1,800	1,494	0	50	40	0
Product16	226	2	0	11	0	0
Product17	3,130	1,822	0	244	171	0
Product18	109	98	0	4	3	0
Product19	82	81	0	12	11	0
Product20	533	250	0	109	88	0
Product21	149	1	0	8	2	0
Product22	1	0	0	1	0	0

Regards

Louw

[fernandotoledo]

Hi

I tried where and it worked well

I think the problem in your case is that you may be using YEARMONT field as a dimension in your chart. In this case you should use sum(TOTAL {$<...>} VALUE) and use YEARMONT = {VARIABLE} instead {'VARIABLE'}

Regards

Fernando

[Anonymous]

I'm the same proble...set analysis seems not work with variables..

I've writed: sum({$<YEAR={$(my_year)}> VALUE}, but doesn't works....

Instead: sum({$<YEAR={2009}> VALUE} seems to work correctly...

Maybe the same problem...have you solved it?

Thank you,

Matteo

[#4]

Hi Fernando, i have tried your suggestion but it gives me an error in the expression box ? any idea

=sum(TOTAL {$<FORMASPENFLAG = {'A'},YearMonth = {$(vCompareDate)}>} Quantity)

Regards

Louw

[#5]

Hi Matteo, no i have not solve it yet - my problem is my yearmonth have to be a variable because it calculate months back - say it is 201001 it must show me the sum of quantities 2 months back but in the dimension 201001.

Hopefully we have a solution soon 🙂

Regards

Louw

[fernandotoledo]

See...

[#7]

Hi Fernando,

Thank you for the sample. That is not 100% what we are looking for instead of selecting a yearmonth to pass through as a variable the variable is the yearmonth in the dimension - to explain better

If the yearmonth is used as a dimension each record will have a different value in variable - for instance 1 st record 200909

2 nd record 200910

3 rd record 200911 and so forth

That is why the total doesn't work - it used one yearmonth value in the variable and used it in every record.

Regards

Louw

[partner_enginee]

Hi, I think to have the same problem.

the user wants to compare period like 2009/12 and 2010/01 with 2010/12 and 2011/01.

The use will select 2010/12 and 2011/01, then I defined a variable vPreviousYearMonth that returns 2009/12 and 2010/01.

But I don't make my espession:

= Count ({$<[Year Month]= {$(vPreviousYearMonth)}>}CMCONTRACTID)

doesn't work. Do you resolve your issue? Let me know. Thank

[sparur]

hello, can you post your qvw, or can you explain how you try to calculate vPreviousYearMonth variable?

[#10]

A QVW WOULD BE NICE YES

SHOULD YOUR EXPRESSION BE:

= Count ({$<[Year Month]= {$(=vPreviousYearMonth)}>}CMCONTRACTID)????

YOU MISSED THE '='????