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: 
kiru_18
Contributor II
Contributor II

How to find/count number of days for selected months and year without AGGR

Hi,

I am using Sum(Aggr(Day(MonthEnd(Max(DateSet))), Month, Year)) to get total number of days for selected month and year. Can we use set analysis to achieve this?

Example : If I select Feb 2020, Mar 2020, Feb 2021, Mar 2021 , Result  - no of days = 119

     If I select Jan 2020, May 2020. Result - no of days = 62.

Can anyone please help me to get this without using AGGR in expression.

Labels (2)
6 Replies
Gysbert_Wassenaar

perhaps like this: Max( DateSet) - Min(DateSet) + 1


talk is cheap, supply exceeds demand
kiru_18
Contributor II
Contributor II
Author

It's not working. Giving wrong answer. If i select Jan 2021 and Mar 2021 it's showing 90 days. But result should be 31(for Jan)+31(for Mar) = 62 days

kiru_18
Contributor II
Contributor II
Author

Can anyone give solution with if statement or set expression. Ques: Count number if days for selected month(multiple selection in list box)

EX: If Mar 2021 and 2020 are selected. Output should be 31+31 = 62 days

  If Jan 2020, Feb 2020 and June 2020 are selected, then Output should be 31+29+30 = 90 days

marcus_sommer

I wouldn't try it in this way else using appropriate information within a master-calendar, like defining a 1 for each calendar/working-day and then just applying sum(DayValue) or you may also calculate the max/kum days there and using max/avg() to pick them.

- Marcus

kiru_18
Contributor II
Contributor II
Author

If i didn't have record on particular date then result is wrong. If May month has only records for 20 date. Then if I select May month it's show sum(dayvalue) is 20. But result I need to get is total days in month (i.e 31 days in May month) 

MayilVahanan

HI @kiru_18 

Hope you've Date field in your data model which is derived from Master Calendar. 

In that case, You can use

count(Distinct Date)

Suppose, you don't have records in fact table & dimension table info, you can remove the those selections with help of set analysis.

Count({<YourDim1=, YourDim2=>}Distinct Date)

Else, you need to create dummy value for missed dates in your fact table to resolve this.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.