Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Set Analysis using variable for yearmonth expression error

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 MONTH200911200911200911200912200912200912
=AddMonths( (MakeDate( left(YearMonth,4),right(YearMonth,2))) ,-2)2009/09/012009/09/012009/09/012009/10/012009/10/012009/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 )200909200909200909200910200910200910
FOCUS BRANDForecast Order QuantityQuantityPREV QUANForecast Order QuantityQuantityPREV QUAN
230,629129,1830144,50683,3210
Total47,07525,08803,9812,1810
Product186001310
Product17900200
Product2146146099990
Product372720100
Product49084840100800
Product5617610017170
Product615100200
Product74200100
Product8717610015120
Product99957540109960
Product105700100
Product114400200
Product122157401760
Product131,188902082260
Product142,1901,832070600
Product151,8001,494050400
Product16226201100
Product173,1301,82202441710
Product18109980430
Product198281012110
Product205332500109880
Product2114910820
Product22100100


Regards

Louw



13 Replies
fernandotoledo
Valued Contributor

Set Analysis using variable for yearmonth expression error

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

matteofioroni
Contributor

Set Analysis using variable for yearmonth expression error

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

Not applicable

Set Analysis using variable for yearmonth expression error

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



Not applicable

Set Analysis using variable for yearmonth expression error

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
Valued Contributor

Set Analysis using variable for yearmonth expression error

See...

Not applicable

Set Analysis using variable for yearmonth expression error

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
Partner

Set Analysis using variable for yearmonth expression error

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
Valued Contributor II

Set Analysis using variable for yearmonth expression error

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

Not applicable

Set Analysis using variable for yearmonth expression error

A QVW WOULD BE NICE YES

SHOULD YOUR EXPRESSION BE:

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

YOU MISSED THE '='????