topic Re: Chart Date Dimension with Most Recent Received as Measure in App Development

Bar Chart Date Dimension with Most Recent Received as Measure

epar5lcd — Sat, 16 Nov 2024 18:21:04 GMT

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.

Re: Chart Date Dimension with Most Recent Received as Measure

Kushal_Chawda — Thu, 16 Jul 2020 23:47:02 GMT

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:

Re: Chart Date Dimension with Most Recent Received as Measure

epar5lcd — Fri, 17 Jul 2020 16:00:17 GMT

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.

Re: Chart Date Dimension with Most Recent Received as Measure

Kushal_Chawda — Fri, 17 Jul 2020 21:19:46 GMT

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))

Re: Chart Date Dimension with Most Recent Received as Measure

epar5lcd — Mon, 20 Jul 2020 17:19:58 GMT

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.

Re: Chart Date Dimension with Most Recent Received as Measure

epar5lcd — Wed, 22 Jul 2020 16:22:06 GMT

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]))

Re: Chart Date Dimension with Most Recent Received as Measure

Kushal_Chawda — Wed, 22 Jul 2020 21:47:25 GMT

try below

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

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

Re: Chart Date Dimension with Most Recent Received as Measure

epar5lcd — Fri, 24 Jul 2020 14:23:58 GMT

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.

Re: Chart Date Dimension with Most Recent Received as Measure

Kushal_Chawda — Fri, 24 Jul 2020 15:36:18 GMT

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

Re: Chart Date Dimension with Most Recent Received as Measure

epar5lcd — Fri, 24 Jul 2020 15:52:20 GMT

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?

Re: Chart Date Dimension with Most Recent Received as Measure

Kushal_Chawda — Fri, 24 Jul 2020 16:16:59 GMT

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?

Re: Chart Date Dimension with Most Recent Received as Measure

epar5lcd — Fri, 24 Jul 2020 16:24:54 GMT

@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.

Re: Chart Date Dimension with Most Recent Received as Measure

epar5lcd — Fri, 24 Jul 2020 19:25:38 GMT

@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.

Re: Chart Date Dimension with Most Recent Received as Measure

Kushal_Chawda — Fri, 24 Jul 2020 20:53:15 GMT

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?

Re: Chart Date Dimension with Most Recent Received as Measure

Kushal_Chawda — Fri, 24 Jul 2020 22:14:03 GMT

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)

Re: Chart Date Dimension with Most Recent Received as Measure

Kushal_Chawda — Fri, 24 Jul 2020 22:42:01 GMT

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

Re: Chart Date Dimension with Most Recent Received as Measure

epar5lcd — Mon, 27 Jul 2020 12:47:21 GMT

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.