topic Re: Getting number of days for selected months and year without AGGR in QlikView

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

kiru_18 — Sun, 09 May 2021 16:32:59 GMT

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.

Re: Getting number of days for selected months and year without AGGR

Gysbert_Wassenaar — Sun, 09 May 2021 08:32:54 GMT

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

Re: Getting number of days for selected months and year without AGGR

kiru_18 — Sun, 09 May 2021 16:25:26 GMT

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

Re: Getting number of days for selected months and year without AGGR

kiru_18 — Sun, 09 May 2021 16:31:37 GMT

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

Re: Getting number of days for selected months and year without AGGR

marcus_sommer — Mon, 10 May 2021 11:39:54 GMT

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

Re: Getting number of days for selected months and year without AGGR

kiru_18 — Wed, 12 May 2021 07:41:55 GMT

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)

Re: Getting number of days for selected months and year without AGGR

MayilVahanan — Wed, 12 May 2021 07:50:00 GMT

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.