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