How to extract the last date value of every quarter in qlik sense

[Prakharsharma]

HI All,

I want to extract the Last Date value of Every fiscal Quarter in Bar Chart.

For Eg.

Quarter           Date                  Value

Q3            1 December 2021    36

Q3             2 December 2021    56

Q3             3 December 2021    798

Q3             31 December 2021    334

Q1                 1 April 2022          34

Q1                2 April 2022            45

Q1                30 April 2022         50

Q1             1 May 2022              342

Q1            2 May 2022               242

Q1             3 June 2022             7224

Q1            31 June 2022            21342

Q4            2 Jan 2022                 231

Q4             3 Jan 2022                 433

Q4           31 Jan  2022              5432

Q4            2 March 2022                 1231

Q4             3 March 2022                 1325

Q4           31 March  2022              6547

In this i  have to make a bar chart in which only the last date value of every fiscal quarter is needed.

Could anyone let me know how to extract the last date value of every quarter.

Thanks

[eddie_wagt]

Use this as an expression 

=FirstSortedValue(aggr(Sum(Value),Date,Quarter),-Date)

Kind regards

Eddie

If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.

[Prakharsharma]

This expression is not working. It was showing 0 in chart.

[eddie_wagt]

I think it has something to do with your date format. To use this
expression correct you will have to have a good date for it.

I have manually changed your dates

LOAD * INLINE [Quarter, DateTxt,Value, Date

Q3,1 December 2021, 36, 01-12-2021

Q3, 2 December 2021, 56, 02-12-2021

Q3, 3 December 2021, 798, 03-12-2021

Q3, 31 December 2021, 334, 31-12-2021

Q1, 1 April 2022, 34, 01-04-2022

Q1, 2 April 2022,45, 02-04-2022

Q1, 30 April 2022,50, 30-04-2022

Q1, 1 May 2022, 342, 01-05-2022

Q1,2 May 2022,242, 02-05-2022

Q1, 3 June 2022, 7224, 03-06-2022

Q1,31 June 2022,21342, 31-06-2022

Q4,2 Jan 2022, 231, 02-01-2022

Q4, 3 Jan 2022, 433, 03-01-2022

Q4, 31 Jan 2022, 5432, 31-01-2022

Q4,2 March 2022, 1231, 02-03-2022

Q4, 3 March 2022, 1325, 03-03-2022

Q4, 31 March 2022, 6547, 31-03-2022
];

[Prakharsharma]

Hi,

I am using 

FirstSortedValue(aggr(count(distinct business_term_id),date_key,FiscalQuarter),-date_key)

this expression an in Dimension i am usimg simply FiscalQuarter

Format of date_key is DD-MM-YYYY

Still it was showing 0 in chart.

Can you please help what i have to change in expression

Thanks

[eddie_wagt]

Ai, you have other dimensions than provided in your example. The expression
works with the data you provided. Now it is a bit more complicated. It
depends really on which dimensions you present in the table and it depends
on the uniqueness of the date_key.

If you have more dimensions just put this in the aggr function.
FirstSortedValue(aggr(count(distinct
business_term_id),date_key,FiscalQuarter, [field2], [field3]),-date_key)

If the date is not unique (for example two values for a date) and probably
it is not unique than do something like this
=FirstSortedValue( aggr(count(distinct
business_term_id),date_key,FiscalQuarter, [field2], [field3]), -aggr(only(
date_key ), date_key , FiscalQuarter ))