Bar Chart Date Dimension with Most Recent Received as Measure

[epar5lcd]

I am trying to chart the most recent date a sensor is reporting. My dimension is a master calendar that has custom 'year/seasons' that I use in a drill down. The goal is to show when a particular sensor stopped reporting. I have about 84K records with the following structure:

Tower ID	Sensor ID	Event ID	Report	Milestone	Submittal Date
200	B	100	4	IN	6/5/1985
300	J	101	4	IN	8/13/1986
100	A	102	2	IN	8/24/1987
400	H	103	3	IN	12/29/1987
100	A	102	2	OUT	3/21/1988
400	E	104	7	IN	7/14/1988
300	G	105	3	IN	8/31/1988
500	I	106	2	IN	2/21/1989
100	D	107	3	IN	6/1/1989
100	C	108	6	IN	6/1/1989
200	B	109	8	IN	6/7/1989
100	C	110	9	IN	6/9/1989
500	I	106	2	OUT	7/17/1989
500	F	111	4	IN	8/9/1989
100	C	112	3	IN	9/18/1989
500	F	113	1	IN	9/25/1989
400	H	114	2	IN	10/2/1989
300	J	115	2	IN	10/2/1989
300	G	116	5	IN	10/2/1989

A tower can have multiple sensors, a sensor can report multiple times (each with an event ID), certain reports may trigger a response (IN/OUT). More than one report can be generated by a sensor in a day, reports are a DUAL value (listed above as a number for simplicity). When multiple reports are generated in one day, the highest value report takes precedent. 

Below is a bar chart showing ALL reports received to date by simply plotting date as a dimension and Count( [Sensor ID]) as measure. 

My goal is to generate a bar chart like the one below (this was not done in Qlik) that shows the count of the last report received for a sensor aggregated by year. Comparing the two charts, of the 36 reports received in 1987 (above), 35 sensors have reported since, only one (below) has a most recent report dated to 1987.

In the above table example I am trying to get this result in the sample data table:

Tower ID	Sensor ID	Event ID	Report	Milestone	Submittal Date
100	A	102	2	IN	8/24/1987
200	B	109	8	IN	6/7/1989
100	C	112	3	IN	9/18/1989
100	D	107	3	IN	6/1/1989
400	E	104	7	IN	7/14/1988
500	F	113	1	IN	9/25/1989
300	G	116	5	IN	10/2/1989
400	H	114	2	IN	10/2/1989
500	I	106	2	IN	2/21/1989
300	J	115	2	IN	10/2/1989

The table above corresponds to Most Recent IN Report by Distinct Sensor ID. I have tried calculating the measure using both MAX() and FirstSortedValue(). Below are a few formulas I have tried with no luck.

Count(Aggr(Max([Submittal Date]), [Sensor ID]))

Count(Aggr(FirstSortedValue([Sensor ID], -[Submittal Date]), [Sensor ID]))

Count(IF([Submittal Date]=Aggr(Max([Submittal Date]), [Sensor ID]), [Sensor ID]))

Count(IF(Aggr(NODISTINCT Max([Submittal Date]), [Sensor ID]) = [Submittal Date], [Sensor ID]))

I am aware that I will need to add set analysis to remove my 'OUT' milestones {<[Milestone]={'IN'}>}. I also noticed that when I drill down most of these formulas recalculate most recent based on the current selection so instead of single value for 1988 (sample data) I end up with two values after drilling down (example below using whole data set and the formula "Count(IF([Submittal Date]=Aggr(Max([Submittal Date]), [Sensor ID]), [Sensor ID]))" that is miscalculating). I assume that I need a TOTAL or {1} somewhere to correct this.

Thank you for the help.

I have updated this post with a sample of the data set. Doing a set analysis on the 'Flag' data field should synthetically generate the results I am looking for.

[Kushal_Chawda]

Yes. But there will be some logic and of course some number to validate the count right? So for example in year 1988 as per your image of bar chart how number 1 came? 

[epar5lcd]

@Kushal_Chawda

I was just retesting and actually the second equation is almost there, there must have been an issue with the data reload when I created 'DUAL' for [Report].

Count({<[Milestone]={'IN'}>}aggr(IF([Submittal Date]=Max(TOTAL<[Sensor ID]>{<[Milestone]={'IN'}>}[Submittal Date]),[Sensor ID]),[Sensor ID],[Submittal Date])).

It is accurately selecting for 'LAST', the issue now is on the drill down. When filtering by year, expected values should still be for 'LAST' of the whole data set, not 'LAST' for that year.

[epar5lcd]

@Kushal_Chawda , figures below show how your second equation (EQ2) generates the desired output. Bars in green are from my results calculated outside of Qlik and red is your equation (keep in mind I am using my custom calendar so your results might look a little different using standard Qlik calendar). On the first chart the total count for 1989 is 9.

Once drilled in, output numbers are wrong. Expected for the 1989 season should still be 9, EQ2 calculates 59. I know this is because it is recalculating 'LAST' for this specific season, but it should still be 'LAST' for the whole data set. I have been experimenting with placing  {1} to force analysis on the full set of all the records in the application, irrespective of any selections made but haven't had any luck.

I really appreciate your continued engagement.

[Kushal_Chawda]

When you say drill down what are the selections you are selecting? Do you want that chart to be static and not to affect by any filter?

[Kushal_Chawda]

meanwhile can you also try script approach

Data:
LOAD
    "Tower ID",
    "Sensor ID",
    "Event ID",
    Report,
    Milestone,
    "Submittal Date"
FROM [lib://Data/Qlik Sample Data.xlsx]
(ooxml, embedded labels, table is Submittals);

Left Join(Data)
Load  "Sensor ID",
      date(max("Submittal Date")) as "Submittal Date",
      1 as Sensor_Flag
Resident Data
where Milestone ='IN'
Group by "Sensor ID";

Then you can use below expressio in chart
=sum(Sensor_Flag)

[Kushal_Chawda · Accepted solution]

Expression will be below

Count({<[Milestone]={'IN'},Year>}aggr(IF([Submittal Date]=
Max(TOTAL<[Sensor ID]>{<Year,[Milestone]={'IN'}>}[Submittal Date]),[Sensor ID]),[Sensor ID],[Submittal Date]))*avg(1)

Also make sure that you have uncheck option of "Include-zero values" in graph

[epar5lcd]

Thank you @Kushal_Chawda that is the solution.  I don't think I would have ever arrived at it on my own, your help has been invaluable.

I have not attempted to do many scripted solutions in Qlik. Would the script still work if there is an external data connection that is pushing updated data to the app?

Thanks once again.