Consultant Analysis - Placement and Vacancy Count per Date

[cosimaeo]

Hi all,
I have been despairing of a report for weeks. The report is supposed to calculate for each consultant how many vacancies and placements he has created in a certain period (which you have to be able to select) and how many opportunities and notes with the content "BD Call", "Initial 1 on1 " and "Prosepecting Call". So I have inserted Placement ID and Vacancy ID and then set it to count distinct so that it is counted. Now I have also inserted "Date Job added" and "Date added Placement" and created a simple prompt via filter. If I then set all filters to 01.03.2024 to 26.03.2024, for example, nothing is displayed for the Consultant MT under Placement, although I know that he had a placement in this period. It seems that vacancies and placements are somehow related in the report. Or is my thought process correct, that the many date filters refer to the respective element - i.e. "Date Job Added" looks at the vacancies and then displays 1 vacancy for the period and does not affect the other columns? Can you please help me with this? Thank you!

[cognostechie]

This does not give us enough information to troubleshoot. Please provide the table structure and relationship between them (joins). Field names and which field/fields makes the data unique. How many queries do you have in the report and if you have multiple queries, joins/unions etc. between them.

We have to know which tables(s) have the dates, IDs etc. What's the expression of the query item/data item that has count distinct. In ver 10, you can have something like:

count (distinct Case When .... Then [Placement ID] End ). Set the aggregate property to 'calculated'

                OR

Case When .... Then [Placement ID] End. Set the aggregation property to 'count distinct'

In order to make this work, the relationship between tables have to correctly defined and the filter(s) have to applied to the correct query subjects/report queries