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]

try below measure for  [Submittal Date]

=if([Submittal Date] =
max(total <[Sensor ID]> {<Milestone={'IN'}>}aggr(max({<Milestone={'IN'}>}[Submittal Date]),[Sensor ID],[Event ID])),[Submittal Date],0)

check below option in chart

Output:

[epar5lcd]

Hi @Kushal_Chawda, thank you for the suggestion. I tried your formula but it does not give me the desired results, I updated my initial post to provide more clarity in what my objectives are. Specifically I am trying  to create a Bar Chart, the Table Data would be a reflection of how the data set is transformed based on user selection.

[Kushal_Chawda]

Can you tell me what should be the output based on your above data in  post meanwhile try below

Count(aggr(IF([Submittal Date]= Max(total <YearMonth>[Submittal Date]), [Sensor ID]), [Sensor ID],YearMonth))

[epar5lcd]

Hi  @Kushal_Chawda ,

I tried your most recent suggestion but that does not appear to work either. My desired output is a bar chart. The image below reflects results using the small sample data I provided above. Red bars are my objective, blue bars included for reference.

 

For this example I am using the autocalendar so you can easily replicate the results (in my working app I use a scripted master calendar). The dimension is a drill down from [Submittal Date.autoCalendar.Year] to [Submittal Date.autoCalendar.YearMonth]. Blue Measure is simply Count({<Milestone={'IN'}>}[Sensor ID]). Red Measure SHOULD count the Max (most recent) date a report came 'IN' for each sensor. The sum total of the red measure count should be equal to the count of distinct sensors (10). Looking at the data, two reports were received in 1987 but only Sensor A's most recent report is counted ('OUT' milestones are ignored) . Sensor E is the only one with a most recent report in 1988. The logic should be that if Sensor E reports again in a future year, say 1990, that count would move out of 1988 and into 1990. This give a picture of what sensors are no longer reporting while active sensors should all have reports in the current year.

Drilling down into 1988 there should still only be one value for the red measure, sensor E's report. 

I hope this makes the problem clearer.

[epar5lcd]

I have been working on logically identifying the values I am looking for. I found that the following statement flags the correct number of sensors and most recent dates but I am not able to apply Count() due to nesting restrictions.

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

In the process I discovered something about the data set I was not aware of. Certain events can generate multiple reports on the same day. Part of the issue is on how to handle these reports when they happen to be the most recent received. This creates a need for a report hierarchy so that the most important report in a group is selected over the rest.

The next equation provides correct total counts for my bar chart but the Submittal Dates that are selected are not consistently the most recent. I even substituted Max() for Min() and get the same results.

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

[Kushal_Chawda]

try below

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

or

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

[epar5lcd]

Hi @Kushal_Chawda,

Thanks you for your continues suggestions. Still no luck. Both formulas you provided are under reporting the suggested value. I updated my original post to include an attachment of the data set. If you do a set analysis on the 'Flag' field you should be able to synthetically generate the results.

[Kushal_Chawda]

Can you also tell me expected output for one example? Like for particular year what should be the latest submit date and counts?

[epar5lcd]

Hi @Kushal_Chawda, the challenge is that the expected output is a function of the whole data set. So if a sensor last reported in 1990, I would expect a 'LAST' count of 1 for that year. But if that same sensor reports today, that original 'LAST' count allocated to 1990 is no longer valid. I would expect the 'LAST' count of 1 for that sensor to now appear in the current year.

If you were to pick a subset of the whole data set I provided, say between the years 2000 to 2005, the majority of 'LAST' counts would be in 2005. Does this make sense?