Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

Adding Months

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

1 Solution

Accepted Solutions
Highlighted
Honored Contributor III

Re: Adding Months

Hi Rehan,

see attachment.

Regards,

Antonio.

View solution in original post

9 Replies
Highlighted
Esteemed Contributor III

Re: Adding Months

May be this?

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

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

Highlighted
Partner
Partner

Re: Adding Months

Sorry doesn't work

Highlighted
Esteemed Contributor III

Re: Adding Months

Can you share a sample file to look into ?

Highlighted
Partner
Partner

Re: Adding Months

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

Highlighted
Partner
Partner

Re: Adding Months

Attached are the sample files

Highlighted
Partner
Partner

Re: Adding Months

Just added the sample xls and qvw

Highlighted
Honored Contributor III

Re: Adding Months

Hi Rehan,

see attachment.

Regards,

Antonio.

View solution in original post

Highlighted
MVP
MVP

Re: Adding Months

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)

Highlighted
MVP
MVP

Re: Adding Months

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)