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

[#2]

Hi

A tricky problem with variables in set analysis is that depending on what they contain, you need to use different ways of extracting it.

I always put the dollar sign expansion in a textbox and see if I get the correct result.
In your example, the result you are after is a number and hence you should not need to use the dollar sign expansion at all but can treat the variable as a list.

sum( {$< FORMFLAG = {'A'},YearMonth = {CompareDate} >} Quantity )

Remember in set analysis, if you are assigning values to a column, multiple numeric values is written as {1,2,3,...} while string values are written as {'a','b',...} with hyphens around them.

Give it a try

[partner_enginee]

Sorry I'm new in QlikView.

In my script, I define vPreviousYearMonth like:

SET vPreviousYearMonth=Year(AddMonths(MakeDate(left([Year Month],4),right([Year Month],2)), -12))&'/'& Month(AddMonths(MakeDate(left([Year Month],4),right([Year Month],2)), -12));

where [Year Month] contains nel user selection of year month like 2010/02, 2010/01,2009/12.

C.

[sparur]

Hello,

I think you should try this expression in your chart: Count ({$<[Year Month]= {'$(vPreviousYearMonth)'}>}CMCONTRACTID)

after this you can compare one current period with this period in last year, for example 2010/02 vs 2009/02.

[partner_enginee]

Thanks, I resolved.

I used a new variable vListYearMonth

vListYearMonth=chr(39) & GetFieldSelections([Year Month],chr(39)&','&chr(39),1000)&chr(39)

then I make my expression like

=

count({1<[Next Year Month]={'$(vYearMonth)'}>}CMCONTRACTID)

where [Next Year Month] is a new field on my table equals to [Year Month] related to next year.

That solution is very clear.

C.