topic Re: Set analyse; Exclude current Month in calculation in QlikView

Set analyse; Exclude current Month in calculation

Anonymous — Thu, 30 May 2013 10:01:59 GMT

Hello,

I have a small question regarding set analyse.

I have a range of Sales per period but I what to excluded the current period

This is de set analyse in the calculation: sum({$<Material={'V','W'}, Period ={">=$(#=year(today())-1)"}>} Act_EUR)

So from today

201305 (may) shoud not be reported, only from june

Month	Act EUR	Pln EUR
201201	120443756	0
201202	120003347	0
201203	118483337	0
201204	117880775	0
201205	115717410	0
201206	116511287	0
201207	115931651	0
201208	115956548	0
201209	114215877	0
201210	114746434	0
201211	114480302	0
201212	112085133	0
201301	112318934	35089738
201302	111633952	35602124
201303	111700319	36114510
201304	111420942	36626896
201305	110001285	37139283
201306	0	37651669
201307	0	38164055
201308	0	38676441
201309	0	39188827
201310	0	39701214
201311	0	40213600
201312	0	40729860

Looking forward.

Best regards,

Robert

Re: Set analyse; Exclude current Month in calculation

swuehl — Thu, 30 May 2013 10:19:29 GMT

Robert,

$(#=year(today())-1) is returning a year, i.e. 2012, isn't it? While you want to create a period, i.e. YearMonth, 201306, right? So try something like

sum({$<Material={'V','W'}, Period ={">=$(#=Date(Monthstart(today(),1),'YYYYMM'))"}>} Act_EUR)

Re: Set analyse; Exclude current Month in calculation

Anonymous — Thu, 30 May 2013 12:20:56 GMT

Hi,

Thanks for the reply,

I’m not quite experience yet to set expressions especially date

This is the orginal statement:

sum({$<Rep_Plant={'V','W'}, Rep_Year={">=$(#=year(today())-1)"}>} Act_MAT_EUR)

But this will not exclude the current month May.

This if statement is not nice but works:

if(Rep_YrMnth= '201305',sum({$<Rep_Plant={'V','W'}, Rep_Year={">=$(#=year(today())-1)"}>} Act_MAT_EUR),0)

Crap of course…;-)

Do you have an answer? Thank

Robert

Robert Waterval

ICT

Koninklijke Mosa bv

Meerssenerweg 358

P.O. Box 1026

NL-6201 BA Maastricht

T +31 (0)43 368 8803

F +31 (0)43 363 11 77

robert.waterval@mosa.nl

www.mosa.nl<http://www.mosa.nl/>

<http://www.facebook.com/mosatiles> <http://www.linkedin.com/company/royal-mosa>

Van: swuehl

Verzonden: donderdag 30 mei 2013 12:20

Aan: Robert Waterval

Onderwerp: - Re: Set analyse; Exclude current Month in calculation

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Set analyse; Exclude current Month in calculation

created by swuehl<http://community.qlik.com/people/swuehl> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/351582#351582>

Re: Set analyse; Exclude current Month in calculation

swuehl — Thu, 30 May 2013 12:47:05 GMT

Sorry, I am not very clear about your requirements and setting, could you post a small sample app and your expected outcome?

You could try to exclude the current month like this:

sum({$<Rep_Plant={'V','W'}, Rep_Year={">=$(#=year(today())-1)"}, Rep_YrMnth -= {201305}>} Act_MAT_EUR)

Re: Set analyse; Exclude current Month in calculation

Anonymous — Thu, 30 May 2013 13:08:51 GMT

Hi,

That’s more like it, now Rep_YrMnth -= has to replaced by the current date statement. Date(Monthstart(today()-1),'YYYYMM') something like that so this moves into time.. Is the outcome clear? regards, Robert sum({$<Rep_Plant={'V','W'}, Rep_Year={">=$(#=year(today())-1)"}, Rep_YrMnth -= >} Act_MAT_EUR)

Month Act EUR Pln EUR

201001 0 112726248

201002 0 112225467

201003 0 112482064

201004 0 111977651

201005 0 111473237

201006 0 110973342

201007 0 110472561

201008 0 109971779

201009 0 109470998

201010 0 108977477

201011 0 108469435

201012 0 107971306

201101 0 105373565

201102 0 105855178

201103 0 106329791

201104 0 106808405

201105 0 107308018

201106 0 107772632

201107 0 108229245

201108 0 108697858

201109 0 109183472

201110 0 109665085

201111 0 110150698

201112 0 110609564

201201 120443756 111953674

201202 120003347 112694693

201203 118483337 113435711

201204 117880775 114207730

201205 115717410 114979749

201206 116511287 115720767

201207 115931651 116420786

201208 115956548 117120805

201209 114215877 117892824

201210 114746434 118664842

201211 114480302 119436861

201212 112085133 120238882

201301 112318934 35089738

201302 111633952 35602124

201303 111700319 36114510

201304 111420942 36626896

201305 0 37139283

201306 0 37651669

201307 0 38164055

201308 0 38676441

201309 0 39188827

201310 0 39701214

201311 0 40213600

201312 0 40729860

Robert Waterval

ICT

Koninklijke Mosa bv

Meerssenerweg 358

P.O. Box 1026

NL-6201 BA Maastricht

T +31 (0)43 368 8803

F +31 (0)43 363 11 77

robert.waterval@mosa.nl

www.mosa.nl<http://www.mosa.nl/>

<http://www.facebook.com/mosatiles> <http://www.linkedin.com/company/royal-mosa>

Van: swuehl

Verzonden: donderdag 30 mei 2013 14:47

Aan: Robert Waterval

Onderwerp: - Re: Set analyse; Exclude current Month in calculation

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Set analyse; Exclude current Month in calculation

created by swuehl<http://community.qlik.com/people/swuehl> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/351684#351684>

Re: Set analyse; Exclude current Month in calculation

swuehl — Thu, 30 May 2013 13:19:24 GMT

Try either:

sum({$<Rep_Plant={'V','W'}, Rep_Year={">=$(#=year(today())-1)"}, Rep_YrMnth -= {$(=Date(today(),'YYYYMM') ) }>} Act_MAT_EUR)

sum({$<Rep_Plant={'V','W'}, Rep_YrMnth = {">=$(=Date(yearstart(today(),-1),'YYYYMM'))<$(=Date(today(),'YYYYMM'))"}>} Act_MAT_EUR)

Re: Set analyse; Exclude current Month in calculation

Anonymous — Thu, 30 May 2013 14:24:08 GMT

Hi,

This works. Thanks!

Robert

Robert Waterval

ICT

Koninklijke Mosa bv

Meerssenerweg 358

P.O. Box 1026

NL-6201 BA Maastricht

T +31 (0)43 368 8803

F +31 (0)43 363 11 77

robert.waterval@mosa.nl

www.mosa.nl<http://www.mosa.nl/>

<http://www.facebook.com/mosatiles> <http://www.linkedin.com/company/royal-mosa>

Van: swuehl

Verzonden: donderdag 30 mei 2013 15:20

Aan: Robert Waterval

Onderwerp: - Re: Set analyse; Exclude current Month in calculation

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Set analyse; Exclude current Month in calculation

created by swuehl<http://community.qlik.com/people/swuehl> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/351709#351709>