Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
128718
Contributor II
Contributor II
‎2021-09-30 07:14 AM

Need to get previous quarter value for the selection quarter

 Hi Team, 

My requirement is when I select Q4-2021 in filter I should get Q3-2021(measured value) in KPI object similarly when I select Q3-2021 I should get Q2-2021(measured value) in KPI object.

I have tried

count({<Date={">=$(=QuarterStart(Max(Date),-1))<=$(=QuarterEnd(Max(Date),-1))"}>} sales)  as my measure

but got 

128718_2-1633000371398.png

Expected output:

128718_1-1633000753156.png

 

Could you please help me to achieve the above requirement.

 

Thanks

 

2,212 Views
0 Likes
1 Solution

Accepted Solutions
abhijitnalekar
Specialist II
Specialist II
‎2021-10-01 04:32 AM

In response to 128718

Hi @128718 ,

 

Please follow the below steps to get the required output.

1. Create  new dimension in the script as  Ceil(Month(SatrtDate)/3) as Quart

2. Create 2 variables as maxq with expression : =max(Quart) and PrevQ as expression  : =max(Quart)-1

3. write a expression in KPI as measure for current Quarter :  count({<Quart={"$(maxq)"}>}ID) and

prev Quarter as  count({<Qart={"$(PrevQ)"}>}ID)

 

Hope this helps you.

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

2,074 Views
0 Likes
11 Replies
abhijitnalekar
Specialist II
Specialist II
‎2021-09-30 08:15 AM

HI @128718 ,

 

Try below set analysis

above(sum({<InvoiceDate={">=$(=QuarterStart(Max(InvoiceDate)))<=$(=QuarterEnd(Max(InvoiceDate)))"}>} [#TransactionCount]) )

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
1,816 Views
0 Likes
128718
Contributor II
Contributor II
‎2021-09-30 08:49 AM
Author

In response to abhijitnalekar

Hi @abhijitnalekar 

 

Thanks for your reply.

I have tried the above expression however it is not working 

128718_0-1633006154510.png

 

1,809 Views
0 Likes
abhijitnalekar
Specialist II
Specialist II
‎2021-09-30 08:52 AM

In response to 128718

Can you share a dataset.

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
1,797 Views
0 Likes
abhijitnalekar
Specialist II
Specialist II
‎2021-09-30 08:58 AM

Also, like to understand the formula behind the quarter field.

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
1,793 Views
0 Likes
128718
Contributor II
Contributor II
‎2021-09-30 09:42 AM
Author

In response to abhijitnalekar

Please find the dataset

QuarterIDDateSatrtDateEndDate
01/01/2021to31/03/2021xxx03-01-202101-01-202131-03-2021
01/04/2021to30/06/2021aaa05-05-202101-04-202130-06-2021
01/07/2021to30/09/2021bbb03-07-202101-07-202130-09-2021
01/07/2021to30/09/2021yyy08-08-202101-07-202130-09-2021
01/07/2021to30/09/2021ccc06-08-202101-07-202130-09-2021
01/01/2021to31/03/2021zzz28-02-202101-01-202131-03-2021
01/04/2021to30/06/2021qqq08-04-202101-04-202130-06-2021
01/04/2021to30/06/2021sss01-05-202101-04-202130-06-2021
01/01/2021to31/03/2021ddd27-03-202101-01-202131-03-2021

Quarter field is the concatenation of  Startdate and Enddate

1,781 Views
0 Likes
abhijitnalekar
Specialist II
Specialist II
‎2021-09-30 10:03 AM

In response to 128718

Hi @128718 ,

Please find attached QVF file. I hope you will get your answer.

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
Test.qvf
1,776 Views
0 Likes
128718
Contributor II
Contributor II
‎2021-09-30 10:40 AM
Author

In response to abhijitnalekar

@abhijitnalekar 

 

Sorry I am not getting the exact output. Please find the dataset and output

QuarterIDDateSatrtDateEndDate
01/01/2021to31/03/2021xxx03-01-202101-01-202131-03-2021
01/07/2021to30/09/2021aaa05-05-202101-07-202130-09-2021
01/07/2021to30/09/2021bbb03-07-202101-07-202130-09-2021
01/07/2021to30/09/2021yyy08-08-202101-07-202130-09-2021
01/07/2021to30/09/2021ccc06-08-202101-07-202130-09-2021
01/01/2021to31/03/2021zzz28-02-202101-01-202131-03-2021
01/04/2021to30/06/2021qqq08-04-202101-04-202130-06-2021
01/04/2021to30/06/2021sss01-05-202101-04-202130-06-2021
01/01/2021to31/03/2021ddd27-03-202101-01-202131-03-2021

 

 

Expected Ouput

QuarterCount of Ticket Count of Ticket for Previous Quarter
01/01/2021to31/03/20213-
01/04/2021to30/06/202123
01/07/2021to30/09/202142

 

It would be great help if you solve it.

 

Thanks

1,766 Views
0 Likes
abhijitnalekar
Specialist II
Specialist II
‎2021-09-30 10:54 AM

In response to 128718

Hi,

Please use below expression

Count of Ticket : Aggr(count(ID),Quarter) 

Count of Ticket for Previous Quarter : Above(total Aggr(count(ID),Quarter))

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
1,758 Views
0 Likes
128718
Contributor II
Contributor II
‎2021-09-30 11:05 AM
Author

In response to abhijitnalekar

Hi @abhijitnalekar ,

 

Thank you for your help, in table it is working fine but  my requirement is when I chose the Quarter in filter I should be showing previous quarter value in KPI object.

1,753 Views
0 Likes