topic Re: Adding Months in QlikView

Adding Months

azmeerrehan — Fri, 23 Jun 2017 19:22:23 GMT

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

Re: Adding Months

vishsaggi — Fri, 23 Jun 2017 20:02:51 GMT

May be this?

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

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

Re: Adding Months

azmeerrehan — Fri, 23 Jun 2017 20:18:01 GMT

Sorry doesn't work

Re: Adding Months

vishsaggi — Fri, 23 Jun 2017 20:22:24 GMT

Can you share a sample file to look into ?

Re: Adding Months

azmeerrehan — Fri, 23 Jun 2017 22:31:56 GMT

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

Re: Adding Months

azmeerrehan — Fri, 23 Jun 2017 22:35:23 GMT

Attached are the sample files

Re: Adding Months

azmeerrehan — Fri, 23 Jun 2017 22:35:52 GMT

Just added the sample xls and qvw

Re: Adding Months

antoniotiman — Sat, 24 Jun 2017 04:47:04 GMT

Hi Rehan,

see attachment.

Regards,

Antonio.

Re: Adding Months

MK_QSL — Sat, 24 Jun 2017 12:51:55 GMT

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)

Re: Adding Months

MK_QSL — Sat, 24 Jun 2017 13:12:03 GMT

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)