How to write a set analysis for calculating Number of weeks in respective months

[kunkumnaveen]

Hello All,

Using Today function i need to count how many week are left in the Current month ,Next month and Following month ,

For example today is 3 rd of July so in  July total 31 days  .

Formula (Total days of july -today's date)

In qlik for current month, i have written

=(num(monthend(today()))-num(today()))/7 which gives no.of weeks for current month as 4.14 weeks left

and for next month

(num(addmonths(monthend(today()),1))-num(today()))/7...................................     count= 8.56 weeks left

and for next to next month

(num(addmonths(monthend(today()),2))-num(today()))/7.......................................count= 12.86 weeks left

Using the above logic ,I need to show the count of weeks which will fall under current month ,Next month ,next to next month in three different KPI's

Let say I have a WEEK column as below

Week

2

3

4

5

7

18

10

So according to my requirement ,as per today Weeks which are lesser than or equall to 4.14 should fall in current month (count=3)and

for second month > 4.14 and <= 8.56(count=2), third month >8.56 to <= 12.86 (count=2).

Note :those values are as for today

How should i need to write in Set Analysis to achieve the above requirement

[bramkn]

For Calculations like this it is best to add an extra column in the kalendar with the calculation to reduce load on the front end. With this it is also easy to use set analysis on this new field.

[kunkumnaveen]

Actually i haven't got any calendar columns  in my  Data ,

if i  need write a set analysis at front end ,how should i need to write it?

[kunkumnaveen]

I tried Something like below for current month count in KPI but it not working

if(

[Weeks Left]

<=

((num(monthend(today()))-num(today()))/7),

count([Weeks Left])

)

[kunkumnaveen · Accepted solution]

count({<[Weeks Left] ={"<=$(vCurrentMonth) "}>} [Weeks Left])

vCurrentMonth =((num(monthend(today()))-num(today()))/7)