Aggr() inside the Set analysis

[mahmutgencel]

Hi all,

I have a dataset. I have start and end dates in this dataset. I want to calculate the data in a month with set analysis. I want to do this with the start and end dates of the month in the set analysis. But in the set analysis, the aggr function does not give the result I want. I didn't know if I wrote the set analysis wrong. I am sharing the sample application and dataset. I will be grateful if you could help me.

My Code

count( {<StartDate = {"<=$(=date(max( AGGR ( max(LastDayMonth) , Month ) )))" }, LeftDate = {">=$(=date(min( AGGR ( min(FirstDayMonth) , Month ) )))"}>}

ID)

Regards,

Burak

[Oleg_Troyansky]

Hi Burak,

I think you overly complicated this calculation. I believe the same count can be done a lot easier if you just used the function MonthStart on your dates. 

The AGGR() within Set Analysis would always give you the Max date of all months and the Min date for all months, and that is why the count shows the same number across all lines. 

Also, you should keep in mind that Set Analysis cannot be sensitive to your chart dimensions (i.e. Month) - these conditions are validated globally, outside of the chart.

If your data size allows, you can achieve your result with the IF() condition, or you need to find a way of pre-calculating the corresponding Month in the data load script. Since both dates belong to the same data row, this should be possible.

Since you are trying to use Set Analysis and AGGR for your calculations, I believe you could benefit a lot from my session on Set Analysis and AGGR session at the Masters Summit for Qlik. I will be teaching this session online on March 1st. Check our agenda and register!

[mahmutgencel]

Hi Oleg;

You may be right.
The example I gave is limited data. There are many more years, months and calculations in the original data. I shared sample data and application. I would be very grateful if you could share a sample code.
In the meantime, I will try to attend the summit.
thank you

[Oleg_Troyansky]

What's your logic for determining the Month - Start date AND Leave Date are both in the same Month? What should happen in Start Date and Leave date have different months? Which one should get assigned?

My suggestion would be to implement this condition in the data load script and then show a simple count by month in the chart.

[mahmutgencel]

Actually, what I'm trying to do is like this. Consider the staff list. Employees have entry and exit dates. I'm basically trying to calculate the number of personnel on a monthly basis.

[Oleg_Troyansky · Accepted solution]

OK, this is a bit more involved... Basically you have 2 choices:

1. Generate data for all combinations of Employees and Months, based on Start Date and End Date, and then the count in charts would be easier.

2. Determine on the fly, using an IF() function, which IDs should be counted for each Month. This would fly if your data size is manageable. It shouldn't be used with a large data set. Assuming that you have a Calendar with the fields Date and Year-Month, and you use Year-Month as a dimension, your Measure could look like this:

count( IF( StartDate<=Min(Date) and LeaveDate>= Max(Date) , ID))

This would only include complete months. In order to include partial months, you could fine tune the condition.

Cheers,

[mahmutgencel]

Thanks for your interest

Cheers 🙂,