topic Set Analysis using variable for yearmonth expression error in QlikView

Set Analysis using variable for yearmonth expression error

Wed, 24 Nov 2010 13:48:20 GMT

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

Set Analysis using variable for yearmonth expression error

fernandotoledo — Wed, 24 Nov 2010 14:02:15 GMT

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

Set Analysis using variable for yearmonth expression error

Anonymous — Wed, 24 Nov 2010 15:10:33 GMT

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

Set Analysis using variable for yearmonth expression error

Wed, 24 Nov 2010 15:29:22 GMT

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

Set Analysis using variable for yearmonth expression error

Wed, 24 Nov 2010 15:35:03 GMT

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

Set Analysis using variable for yearmonth expression error

fernandotoledo — Wed, 24 Nov 2010 16:16:17 GMT

See...

Set Analysis using variable for yearmonth expression error

Thu, 25 Nov 2010 12:47:00 GMT

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

Set Analysis using variable for yearmonth expression error

partner_enginee — Fri, 24 Dec 2010 11:30:50 GMT

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

Set Analysis using variable for yearmonth expression error

sparur — Fri, 24 Dec 2010 12:11:25 GMT

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

Set Analysis using variable for yearmonth expression error

Fri, 24 Dec 2010 14:21:09 GMT

A QVW WOULD BE NICE YES

SHOULD YOUR EXPRESSION BE:

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

YOU MISSED THE '='????

Set Analysis using variable for yearmonth expression error

Fri, 24 Dec 2010 20:23:57 GMT

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

Set Analysis using variable for yearmonth expression error

partner_enginee — Mon, 27 Dec 2010 09:40:34 GMT

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.

Set Analysis using variable for yearmonth expression error

sparur — Mon, 27 Dec 2010 09:55:43 GMT

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.

Set Analysis using variable for yearmonth expression error

partner_enginee — Mon, 27 Dec 2010 15:51:53 GMT

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.