Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
kunkumnaveen
Valued Contributor

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

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




1 Solution

Accepted Solutions
kunkumnaveen
Valued Contributor

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

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

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

View solution in original post

4 Replies
Partner
Partner

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

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
Valued Contributor

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

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
Valued Contributor

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

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
Valued Contributor

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

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

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

View solution in original post