Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I was able to find a previous post (link below) that was exactly what I am trying to solve but my current outcome is not the result that I am after. The goal is to only highlight Distinct ID's with the max month. A month number was created to help get the max month ([Month#] = NUM(MakeDate(2020,6)) > one was also created for July > NUM(MakeDate(2020,7))).
Problem is that when I drop this into a straight table, not all of the Emp_ID's will be distinct. Running out of ideas on how to trouble shoot this so any advice will be appreciated. Here is an expression that I am using to isolate just the max month: =Count({<Month#={$(=max([Month#]))}>}DISTINCT [Emp_ID])
Emp_ID | Date | Month# | Status |
1 | Jun-20 | 43983 | Reject |
1 | Jul-20 | 44013 | Approved |
2 | Jul-20 | 44013 | Reject |
3 | Jul-20 | 44013 | Reject |
4 | Jun-20 | 43983 | Reject |
4 | Jul-20 | 44013 | Approved |
5 | Jul-20 | 44013 | Reject |
Below is the target output
Emp_ID | Date | Month# | Status |
1 | Jul-20 | 44013 | Approved |
2 | Jul-20 | 44013 | Reject |
3 | Jul-20 | 44013 | Reject |
4 | Jul-20 | 44013 | Approved |
5 | Jul-20 | 44013 | Reject |
https://community.qlik.com/t5/QlikView-App-Development/Count-items-with-max-date/m-p/292293
@Kushal_Chawda I wanted to provide an update. The problem was that I had a number of different tables stitched together to create the table I am referencing. After doing some detective work, I was able to isolate the problem and work this out.
Once again, so much gratitude for your support. Thank you!
T1:
LOAD
Emp_ID,
Date#("Date",'MMM-YY') as Date,
Month#,
Status
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);
create Straight table
Dimension:
Emp_ID
Expressions:
1) =FirstSortedValue(Date,-Date) ----> Date
2) =FirstSortedValue(Month#,-Date) -----> Month#
3) =FirstSortedValue(Status,-Date) -----> Status
First convert Month# into Date format, currently its in Number format. consider max date group by Emp_ID
@Kushal_Chawda Thank you for your support. I recreated my sample data table and both your expression solutions + my original expression (=Count({<Month#={$(=max([Month#]))}>}DISTINCT [Emp_ID])) worked perfectly. Problem is that when I apply it to my true application, I am still running into the same problem.
My data load has multiple different months. This specific table reference is to only load July 2020 data. However the June 2020 data is leaking through. I did some detective work and discovered that the table is showing the June 2020 data as July 2020. This has me confused because I created specific references for each month:(Ex: MonthName(MakeDate(2020,6))) AS [Date] / Num(MakeDate(2020,6)) AS [Month#] )
Have you ever encountered this? The June 2020 data is coming through as July 2020? How would you approach this?
Emp_ID | Date | Month# | Status |
1 | Jun-20 | 43983 | Reject |
1 | Jul-20 | 44013 | Approved |
2 | Jul-20 | 44013 | Reject |
3 | Jul-20 | 44013 | Reject |
4 | Jun-20 | 43983 | Reject |
4 | Jul-20 | 44013 | Approved |
5 | Jul-20 | 44013 | Reject |
How month is calculated?
@Kushal_Chawda month is calculated with the following:
- Date = MonthName(MakeDate(2020,6))
- Month# = Num(MakeDate(2020,6))
Each new data load for the month has the above listed updated to the corresponding month (ex: July 2020 data was updated to reflect Date / Month# = 2020,7
It looks like month calculation is getting wrong somewhere then. Can you regenerate the data for specific month and replace it and then check how it behaves
@Kushal_Chawda I am looking to see if there are some data load/scripting issues. Just want to thank you for your responses and support. Appreciate you!
@Kushal_Chawda I wanted to provide an update. The problem was that I had a number of different tables stitched together to create the table I am referencing. After doing some detective work, I was able to isolate the problem and work this out.
Once again, so much gratitude for your support. Thank you!
Glad that you were able to sort it out and it worked. Please mark the appropriate answers correct so that it will be helpful for other