Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set analyse; Exclude current Month in calculation

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

MonthAct EURPln EUR
2012011204437560
2012021200033470
2012031184833370
2012041178807750
2012051157174100
2012061165112870
2012071159316510
2012081159565480
2012091142158770
2012101147464340
2012111144803020
2012121120851330
20130111231893435089738
20130211163395235602124
20130311170031936114510
20130411142094236626896
20130511000128537139283
201306037651669
201307038164055
201308038676441
201309039188827
201310039701214
201311040213600
201312040729860

Looking forward.

Best regards,

Robert


6 Replies
swuehl
MVP
MVP

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)


Anonymous
Not applicable
Author

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>

swuehl
MVP
MVP

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)

Anonymous
Not applicable
Author

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>

swuehl
MVP
MVP

Try either:

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

or

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


Anonymous
Not applicable
Author

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>