Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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>
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>
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)
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>
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>
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)
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>
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>