Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor 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
Highlighted

Re: Master calendar month number issue

May be like this

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

View solution in original post

13 Replies
Highlighted

Re: Master calendar month number issue

May be like this

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

Highlighted
Contributor III

Re: Master calendar month number issue

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

Highlighted
Contributor

Re: Master calendar month number issue

You could try this..

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

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

Thanks D

🙂

Highlighted

Re: Master calendar month number issue

May be like this

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

View solution in original post

Highlighted
Contributor

Re: Master calendar month number issue

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

🙂

Highlighted
Contributor

Re: Master calendar month number issue

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

🙂

Highlighted
Contributor III

Re: Master calendar month number issue

Thank u so much Sunny. Its working just fine!

Highlighted
Contributor III

Re: Master calendar month number issue

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

Highlighted
Contributor III

Re: Master calendar month number issue

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?