Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Bar Chart by Dimension with Max Date

I have a data set consisting of Jobs and Statuses with updated datetime joined as follows:

Capture_qq1.PNGCapture_qq5.PNG

My goal is to create a bar chart that shows the count of jobs with their latest status TAG based on TransactionTime within the selected date period (max of FullDate). The count should be 14, but if I add the TAG the count is over 14 due to the one-to-many relationship.

Capture_qq3.PNG  Capture_qq2.PNG

The proper chart should look like this:

Capture_qq4.PNG

My count formula is:

Count(
{<
[tMasterCalendar.FullDate] = {'$(=Max([tMasterCalendar.FullDate]))'}, 

[TRANSACTIONTIME] = {"<$(=Max([tMasterCalendar.FullDate]))"}
>}
[JOBID]
)

How do I get it to show the latest TAG only? Should I add a set that makes TransactionTime = aggregated max of TransactionTime per JobID? Thanks!

 

3 Replies
Highlighted
Partner
Partner

Why do you need [tMasterCalendar.FullDate] = {'$(=Max([tMasterCalendar.FullDate]))'},
how is fulldate field linked to table you are trying to count?

Share a sample data if you can which gives an approximation of you data and data model
Highlighted
Contributor II
Contributor II

Sample data is attached. 

Fulldate field in Calendar is linked to Job table via a DateBridge table with Job ID and pending date. In this report, I filter down to one period (3/1 - 3/14), and I only want to count number of Jobs that are still pending by period end. So the =Max(FullDate) works.

Capture_qq6.PNG

 

The challenge is to also restrict the status to be the latest transaction time that is smaller than the max period date.

Highlighted
Partner
Partner

try this
Count(
{<
[tMasterCalendar.FullDate] = ,
[TRANSACTIONTIME] = {"<$(=Max([tMasterCalendar.FullDate]))"}
>}
[JOBID]
)