Adding Months

[azmeerrehan]

In my current data set  I have a date field 'YYYYMM' . The data I have is a MTD (MOnth to date data). So by default I would like to show the cumulative (YTD)  for Current year and previous year for comparison. 

I have created a variable called vMonthToday = Month(Today()).

I have tried the below expression and its not working .

sum({$<CalendarYear = {$(=Max(CalendarYear)-1),$(=Max(CalendarYear))},CalendarMonthName={"<=$(=vMonthToday)"}>}  Sales).

Desired result

So for example we are in June  so I would like to see

2017 /$65896... (Cumulative for Jan Thru Jun for 2017)

2016/ $75365...(Cumulative for Jan Thru Jun for 2016)

Can some one please help

[vishsaggi]

May be this?

sum({$<CalendarYear = { ">= $(=Max(CalendarYear)-1) <= $(=Max(CalendarYear))" },

            CalendarMonthName = {"<=$(=vMonthToday)"}>}  Sales)

[azmeerrehan]

Sorry doesn't work

[vishsaggi]

Can you share a sample file to look into ?

[azmeerrehan]

how do I attach a file now. I dont get an  option the Reply message??

[azmeerrehan]

Attached are the sample files

[azmeerrehan]

Just added the sample xls and qvw

[antoniotiman · Accepted solution]

Hi Rehan,

see attachment.

Regards,

Antonio.

[MK_QSL]

Are you sure you are looking for below based on the data you have provided?

2017 /$65896... (Cumulative for Jan Thru Jun for 2017)

2016/ $75365...(Cumulative for Jan Thru Jun for 2016)

[MK_QSL]

Use this script

Data:
Load 
  Year, 
  Month,
  num(month(Date#(Month,'MMM'))) as MonthNumber,
  Sales 
FROM
https://community.qlik.com/servlet/JiveServlet/download/1294277-284479/Sample.xlsx
(ooxml, embedded labels, table is Sheet1);

Now use as below

Dimension

Year

Expression

=SUM({<MonthNumber = {"<=$(=Num(Month(Today())))"}>}Sales)