Count Distinct Issue

[egeremia]

Goal of Report I want to make:

For each month in a year, and for each business day (1 to ~20 depending on month) graph the aggregation of the entries we get in. So if on the first business day in November 2019 we got 7 items in, the second day we got 9, and the third we got 3. The line graph would then show three data points: 7, 16, and 19. There would be a separate line for November in the years 2013-2018.

What I have so far:

For each entry in the table I have a unique ID and a timestamp. I used this timestamp to get fields: year, month, day, day of the week (0 for Monday, 6 for Sunday), business day (function that determines if day falls on a weekday or holiday), and updated day (function that determines if the item was received on a non business day, and if so, moves the day to a business day in order to be calculated in the report). These are accurate and completed.

Problems I have:

What I still need to do is for each month, count the distinct amount of updated days, and map 1 - the distinct amount for each day in the month. So if the business days for a month were 2, 3, 6, 7, 8, 10 they would map to 1, 2, 3, 4, 5, 6 and so on. From there I could hopefully graph the aggregation of the number of entries for the business day value. I've tried to test out counting the distinct business days in a random month: march 2013

but when I do I get an error:

I'm still relatively new to qlik and haven't had much experience with distinct and count. Any help would be appreciated, with this error and or how to map these values after I've counted them!

[Vegar]

I think you are missing a group by  in your LOAD statement. 

LOAD 
   QuoteID,
   IF(...) as NumDays
RESIDENT Actuals
Group By
   QuoteID
;

[egeremia]

I still am getting the invalid expression error even with that addition. Thank you nonetheless 

[StarinieriG · Accepted solution]

Hi,

maybe it's because of the if (you have to group by for those dimension too).

You could try in this way:

NumBizDays:

Load QuoteID,

Count(distinct Actual.UpdatedDay)

Resident Actual

Where Actual.Year=2013 and Actual.Month=3

Group by

QuoteID;

[egeremia]

Thank you so much! I used something like this, instead of grouping by QuoteID I grouped by Year and month, that way it would count distinct business days for each month of every year. Using quoteID i got 1 for everything, due to there being only one distinct day that the quote was received on. Now that I have the number of business days in every month, I need to number each date in the month the 1st, 2nd, 3rd, ... business day. Still somewhat new, but would some kind of mapping be required?