Set Analysis expression

[arethaking]

Hi All,

I have the monthly data for the whole year. But I want to show the data until September.

How to write the set analysis expression.

Thanks in advance.

Data:

Amount	Product	Year	Month Name
2	P2	2016	Feb
2	P4	2015	Sep
2	P3	2015	Jun
2	P1	2016	Feb
2	P4	2015	Mar
2	P1	2016	Feb
5	P3	2015	Jun
6	P3	2015	Jan
6	P3	2015	Feb
6	P3	2015	Mar
6	P3	2015	Apr
6	P3	2015	May
6	P3	2015	Jul
6	P3	2015	Aug
6	P3	2016	Mar
6	P3	2016	Apr
6	P4	2016	Jul
7	P3	2015	Jan
7	P3	2015	Feb
7	P3	2015	Mar
7	P3	2015	Apr
7	P3	2015	May
7	P3	2015	Jun
7	P3	2015	Jul
7	P3	2015	Aug
7	P3	2015	Sep
7	P3	2015	Oct
7	P3	2015	Nov
7	P3	2015	Dec
7	P3	2016	Jan
7	P3	2016	Feb
7	P3	2016	Mar
7	P3	2016	Apr
7	P3	2016	May
7	P3	2016	Jun
7	P3	2016	Jul
7	P3	2016	Aug
7	P3	2016	Sep
7	P3	2016	Oct
7	P3	2016	Nov
7	P3	2016	Dec
7	P4	2015	Jan
7	P4	2015	Feb
7	P4	2015	Mar
7	P4	2015	Apr
7	P4	2015	May
7	P4	2015	Jun
7	P4	2015	Jul
7	P4	2015	Aug
7	P4	2015	Sep
7	P4	2015	Oct
7	P4	2015	Nov
7	P4	2015	Dec
7	P4	2016	Jan
7	P4	2016	Feb
7	P4	2016	Mar
7	P4	2016	Apr
7	P4	2016	May
7	P4	2016	Jun
7	P4	2016	Jul
7	P4	2016	Aug
7	P4	2016	Sep
7	P4	2016	Oct
7	P4	2016	Nov
7	P4	2016	Dec
8	P4	2015	Nov
8	P4	2015	Dec
10	P1	2015	Jan
10	P1	2015	Feb
10	P1	2015	Apr
10	P1	2016	Jan
10	P1	2016	Feb
10	P1	2016	Mar
10	P1	2016	Apr
10	P1	2016	May
10	P1	2016	Jun
10	P1	2016	Jul
10	P1	2016	Aug
10	P1	2016	Sep
10	P1	2016	Oct
10	P1	2016	Nov
10	P1	2016	Dec
11	P1	2015	Jan
11	P1	2015	Feb
11	P1	2015	Mar
11	P1	2015	Apr
11	P1	2015	May
11	P1	2015	Jun
11	P1	2015	Jul
11	P1	2015	Aug
11	P1	2015	Sep
11	P1	2015	Oct
11	P1	2015	Nov
11	P1	2015	Dec
11	P1	2016	Jan
11	P1	2016	Feb
16	P2	2015	Jul
17	P2	2015	May
17	P2	2015	Jun
18	P2	2015	Apr
18	P2	2015	Aug
18	P2	2015	Sep
19	P2	2015	Jan
19	P2	2015	Feb
19	P2	2015	Mar
19	P2	2015	Apr
19	P2	2015	May
19	P2	2015	Jun
19	P2	2015	Oct
19	P2	2015	Nov
19	P2	2015	Dec
19	P2	2016	Jan
19	P2	2016	Feb
19	P2	2016	Mar
19	P2	2016	Apr
19	P2	2016	May
19	P2	2016	Jun
20	P2	2015	Jan
20	P2	2015	Feb
20	P2	2015	Jul
20	P2	2015	Aug
20	P2	2015	Sep
20	P2	2015	Oct
20	P2	2015	Nov
20	P2	2015	Dec
20	P2	2016	Jan
20	P2	2016	Feb
20	P2	2016	Mar
20	P2	2016	Apr
20	P2	2016	May
20	P2	2016	Jun
20	P2	2016	Jul
20	P2	2016	Aug
20	P2	2016	Sep
20	P2	2016	Oct
20	P2	2016	Nov
20	P2	2016	Dec
21	P2	2016	Sep
21	P2	2016	Oct
21	P2	2016	Nov
92.830879456	P2	2015	Dec
123.562104384	P4	2016	Feb
171.46285083	P2	2016	Apr
207.918426888	P4	2016	Jul
213.38207968	P2	2015	Jan
255.199400592	P4	2016	May
261.973532776	P4	2015	Sep
330.84459726	P2	2016	May
369.907257888	P2	2015	Sep
370.631483462	P4	2015	Dec
390.766210104	P4	2016	Feb
407.0324552	P2	2015	Jan
418.684886046	P2	2016	Jul
422.546273856	P2	2015	Apr
433.258467264	P2	2015	Mar
450.973911584	P2	2015	Jun
460.07688168	P2	2016	Jan
472.180412256	P2	2015	May
487.236387228	P4	2015	Sep

[tresesco · Accepted solution]

Select Year 2016 and then use the following expression:

Sum({<MonthName={'<=$(=Date(MonthEnd(Today(),-1)))'}>}Amount)

For selection independent YTD, you can try:

Sum({<MonthName={'>=$(=Date(YearStart(Today())))<=$(=Date(MonthEnd(Today(),-1)))'}>}Amount)

And yes, a generic suggestion, use Year() to create Year field in the script instead of Date#().

[amir_taub]

Hi King

if you need some more help ,there is a wizard for SET go to :

Set Analysis Wizard for QlikView | qlikblog.at

Also,  you need to:

1. create a Date Field,

=date(MakeDate(year,month),'DD/MM/YYYY') ---- 01/02/2016

2. Make a VAR 

vDatePast ----  =date(MakeDate(year,month),'DD/MM/YYYY')

3. Do a set ----  Date Field={"<$(vDatePast)"}

good luck

Amir Taub

[Anonymous]

Hi Aretha,

See the document on this post

https://community.qlik.com/docs/DOC-4249

Regards!