Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
epar5lcd
Contributor III
Contributor III

Bar Chart Date Dimension with Most Recent Received as Measure

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 IDSensor IDEvent IDReportMilestoneSubmittal Date
200B1004IN6/5/1985
300J1014IN8/13/1986
100A1022IN8/24/1987
400H1033IN12/29/1987
100A1022OUT3/21/1988
400E1047IN7/14/1988
300G1053IN8/31/1988
500I1062IN2/21/1989
100D1073IN6/1/1989
100C1086IN6/1/1989
200B1098IN6/7/1989
100C1109IN6/9/1989
500I1062OUT7/17/1989
500F1114IN8/9/1989
100C1123IN9/18/1989
500F1131IN9/25/1989
400H1142IN10/2/1989
300J1152IN10/2/1989
300G1165IN10/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. 

epar5lcd_0-1594998244862.png

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.

most recent.png

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

Tower IDSensor IDEvent IDReportMilestoneSubmittal Date
100A1022IN8/24/1987
200B1098IN6/7/1989
100C1123IN9/18/1989
100D1073IN6/1/1989
400E1047IN7/14/1988
500F1131IN9/25/1989
300G1165IN10/2/1989
400H1142IN10/2/1989
500I1062IN2/21/1989
300J1152IN10/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.

most recent q.png

most recent qd.png

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.

1 Solution

Accepted Solutions
Kushal_Chawda

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

View solution in original post

16 Replies
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

Annotation 2020-07-17 004305.png

Output:

Annotation 2020-07-17 004330.png

 

 

epar5lcd
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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.

most recent t .png

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. 

most recent t2 .png

I hope this makes the problem clearer.

epar5lcd
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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?