topic Re: How to get the Current Quarter / Fiscal Period in Set Expression in QlikView

How to get the Current Quarter / Fiscal Period in Set Expression

khigaurav999 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Folks,

I am new to Qlik and looking for ways to get the current quarter value in a set expression. I have following expression where I have hardcoded the values for the current quarter (Q1-2018 below).

=Num(Round(Sum({<[Fiscal Period]={'Q1-2018'}>} DISTINCT [Contractual TOV])/1000), '$#,##0K')

how can I get rid of hardcoded value and make based on current date / system date? I need to get it in the same format as hardcoded here.

Can it be done thru a variable let's vCurrentQtr that is calculated out of some date function that can be reused in other places/expression?

Thanks,

Gaurav

Re: How to get the Current Quarter / Fiscal Period in Set Expression

shiveshsingh — Tue, 13 Mar 2018 06:49:29 GMT

You mean to say..datewise sum?

Re: How to get the Current Quarter / Fiscal Period in Set Expression

khigaurav999 — Tue, 13 Mar 2018 06:52:18 GMT

I just want the CQ value Q1-2018 not to be a hardcoded one. It should come from some date functions... I don't know which function can help in it.

Re: How to get the Current Quarter / Fiscal Period in Set Expression

shiveshsingh — Tue, 13 Mar 2018 06:59:51 GMT

Depends on the fields in your model, do you have any quarter Field defined in model?

Can you share sample app?

Re: How to get the Current Quarter / Fiscal Period in Set Expression

khigaurav999 — Tue, 13 Mar 2018 07:01:06 GMT

No I just want it to based on current system date.

Re: How to get the Current Quarter / Fiscal Period in Set Expression

shiveshsingh — Tue, 13 Mar 2018 07:06:03 GMT

Try this

=Num(Round(Sum({<[Fiscal Period]={">=$(=Num(QuarterStart(Max([Fiscal Period]))))<=$(=Max([Fiscal Period]))"}>} DISTINCT [Contractual TOV])/1000), '$#,##0K')

Re: How to get the Current Quarter / Fiscal Period in Set Expression

Qrishna — Tue, 13 Mar 2018 07:13:32 GMT

Check this out.All the below calculations are based on current date.

Re: How to get the Current Quarter / Fiscal Period in Set Expression

chinnuchinni — Tue, 13 Mar 2018 07:27:13 GMT

try with this,i hope it will help to you.

sum({<OrderDate = {">=$(=Quarterstart(max(OrderDate)))<=$(=max(OrderDate))"}>}Sales)

Re: How to get the Current Quarter / Fiscal Period in Set Expression

Anil_Babu_Samineni — Tue, 13 Mar 2018 07:39:36 GMT

May be this?

Num(Round(Sum({<[Fiscal Period]={'$(=minstring({<[Fiscal year]={"$(max([Fiscal year]))"}>} [Fiscal Period]))'}>} DISTINCT [Contractual TOV])/1000), '$#,##0K')

Re: How to get the Current Quarter / Fiscal Period in Set Expression

khigaurav999 — Tue, 13 Mar 2018 09:06:49 GMT

I think this is what I was looking for:

=Num(Round(Sum({<[Fiscal Period]={$(=chr(39)& 'Q' & CEIL(MONTH(Today())/3) &'-'& YEAR(Today())&chr(39))}>} DISTINCT [Contractual TOV])/1000), '$#,##0K')

It fixes the issue and gives CQ based on system date.

Thanks,

Gaurav