Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arpitkharkia
Creator III
Creator III

Master calendar month number issue

Hi all,

I have a master calendar ranging from Jan-2017 to December-2018. I have generated month number for all the 24 months. The problem i am facing is that Max(MonthNumber) gives me Dec-18, but i want to point at the current month that is Jan-18.

Is there a way i can identify the month number of current month which i can use in set analysis?

Thanks!

1 Solution

Accepted Solutions
sunny_talwar

May be like this

Sum({<MonthYearField= {"$(='<=' & Date(MonthStart(Today()), 'MMM-YY'))"}>}Sales)

View solution in original post

13 Replies
sunny_talwar

May be like this

Date(MonthStart(Today()), 'MMM-YY')

arpitkharkia
Creator III
Creator III
Author

Thanks for the quick reply.

I am using the same thing for current month, and for previous months i am just subtracting from it and getting the required months. But consider the following scenario:

I need to know the

1. sum(sales) - Current month

2. sum(sales) - Previous month

3. sum(sales) - All the rest of the previous months.

The third point is where i am facing problem

darrell_tobin
Creator
Creator

You could try this..

num(     month(<Date>)     )     ) // will return 1 for Jan 18

=Year (<Date>) *100+ num(month(<Date>)) // willl return 201801

Thanks D

🙂

sunny_talwar

May be like this

Sum({<MonthYearField= {"$(='<=' & Date(MonthStart(Today()), 'MMM-YY'))"}>}Sales)

darrell_tobin
Creator
Creator

If you want current then use now() instead of <date>

You could try this..

num(     month(now())     )     ) // will return 1 for Jan 18

=Year (now()) *100+ num(month(now())) // willl return 201801

Thanks D

🙂

darrell_tobin
Creator
Creator

Hi Arpit,

In all my reports with dates fields, I create indexes such as a master field called Month_index using below:

= (

  Num( Year(Date) ) * 12 + Num( Month(Date) )

) // Creates month number for each number

    -

    (

   Num( Year(Now()) ) * 12 + Num( Month(Now()) )

    )// Creates month number for this month

This counts backwards from current month

0 = current month

-1 = last month

-2 = previous month

etc

means you can then use simple set analysis such as

sum(  Sales {$<Month_index  = '0'>}) //current month

sum(  Sales {$<Month_index  = '-1'>}) // previous month

sum(  Sales {$<Month_index  = { '<-1'}>}) // All other previous months

Same can be applied to weeks and years etc...

Thanks

D

🙂

arpitkharkia
Creator III
Creator III
Author

Thank u so much Sunny. Its working just fine!

arpitkharkia
Creator III
Creator III
Author

Thanks for the reply Darrell. This one may also work fine!

arpitkharkia
Creator III
Creator III
Author

Is it possible to select month ranges here?

Like 0-4 months,5-6 months, where

0-Jan-18

1-Dec-17

2-Nov-17 and so on?