topic Re: Pick the most recent Qtr in QlikView

Pick the most recent Qtr

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

Hi ,

I have data as attached sample.

Here I have to pick the most recent QTR and multiply the sum(amount) * 4 for that Qtr.

Exp : In the attached file I have to pick amount from 4/3/2017 to 6/10/2017 and sum(Expense in USD) * 4.

Thanks much.

Re: Pick the most recent Qtr

sunny_talwar — Tue, 20 Jun 2017 12:33:31 GMT

Are we picking 4/3/2017 to 6/10/2017 because it is the current quarter?

Re: Pick the most recent Qtr

Anil_Babu_Samineni — Tue, 20 Jun 2017 12:34:34 GMT

What if you write like below. I haven't check the date

I assume, You may have Quarter field in script

'Q' & Ceil(Month(DateField) / 3) AS Quarter,

Then use any one expression

sum({<Quarter = {'Q4'}>} [Expense in USD]) * 4

sum({<Quarter = {'$(=Max(Quarter))'}>} [Expense in USD]) * 4

Re: Pick the most recent Qtr

sunny_talwar — Tue, 20 Jun 2017 12:37:08 GMT

If the above is true, then may be this

=Sum({<[Entry Date] = {"$(='>=' & Date(QuarterStart(Today())) & '<=' & Date(QuarterStart(Today(), 1)-1))"}>}[Expense in USD]) * 4

Re: Pick the most recent Qtr

prat1507 — Tue, 20 Jun 2017 12:38:59 GMT

You can select the maximum date and use it for finding the start and the end date of the quarter.

max({1}[Entry Date])

Using the above you can find quarter start date as =date(QuarterStart(max({1}[Entry Date]) ))

and quarter end as quarter start date as =date(QuarterEnd(max({1}[Entry Date]) ))

Use the above expressions in a set analysis and you're good to go.

Regards

Pratyush

Re: Pick the most recent Qtr

apthansh — Tue, 20 Jun 2017 12:46:23 GMT

This is for the currebt Qtr right ?

I am looking for the most Recent Qtr...

If I have dates from 1/1/17 through 5/30/17. The latest quarter is 1^st quarter. Sum cost between 1/1/17 and 3/31/17. Multiply by 4 to get a yearly cost.
if I have dates from 11/1/16 to 6/30/17, we would sum the cost from 4/1/17 to 6/30/17, then multiply by 4.

Thanks as always.

sunny‌stalwar1‌

Re: Pick the most recent Qtr

Kushal_Chawda — Tue, 20 Jun 2017 12:47:11 GMT

create Quarter Number in script like below and use it in set

Data:

LOAD CompanyCode,

[Payment Type Org],

SubField([Entry Qtr],'-',3)&SubField([Entry Qtr],'-',2) as QtrNum,

[Entry Date],

[Entry Qtr],

[Expense in USD]

FROM

(biff, embedded labels, table is Sheet1$);

Expression:

=sum({<QtrNum={"$(=max(QtrNum))"}>}[Expense in USD])*4

Re: Pick the most recent Qtr

sunny_talwar — Tue, 20 Jun 2017 12:51:19 GMT

May be this

=Sum({<Quarter= {"$(=Max(Quarter))"}>}[Expense in USD]) * 4

Where Quarter is created in the script like this

Table:

LOAD CompanyCode,

[Payment Type Org],

Date(Date#([Entry Date], 'MM/DD/YYYY')) as [Entry Date],

Year([Entry Date]) & Num(Ceil(Month([Entry Date])/3), '00') as Quarter,

[Entry Qtr],

[Expense in USD]

FROM

[..\..\Downloads\Test.xls]

(biff, embedded labels, table is Sheet1$);

Re: Pick the most recent Qtr

antoniotiman — Tue, 20 Jun 2017 13:17:55 GMT

Hi,

without change script, may be

Sum({<[Entry Date]={'>=$(=QuarterStart(Max([Entry Date])))<=$(=QuarterEnd(Max([Entry Date])))'}>} [Expense in USD])

Regards,

Antonio

Re: Pick the most recent Qtr

apthansh — Tue, 20 Jun 2017 14:07:34 GMT

Thank you Sunny and Kushal

This works great based on my initial request,bu now when I thought about it I see issue with recent qtr ending date.

Here Recent qtr ending on 6/10/2017 - so we wont get amount for full qtr....so here we have to pick date from 1/1/2017 to 3/10/2017 (as this has amount for the whole Qtr - Qtr1).

So basically the logic is to pick amount based of Most Recent full Qtr.

Let me know if I am confusing you.Thank you.

sunny‌stalwar1‌kush141087‌

Re: Pick the most recent Qtr

Kushal_Chawda — Tue, 20 Jun 2017 14:10:14 GMT

ok let me try. till then I think Sunny will have already replied

Re: Pick the most recent Qtr

apthansh — Tue, 20 Jun 2017 14:11:27 GMT

Haha..I know...he is a genius You too

Re: Pick the most recent Qtr

sunny_talwar — Tue, 20 Jun 2017 14:23:35 GMT

How do you know if a quarter is full? I see 3/27/2017... how would I know if this is full? May be 6/10/2017 is full and you are not going to get any values after this date in June?

Re: Pick the most recent Qtr

Kushal_Chawda — Tue, 20 Jun 2017 14:29:17 GMT

For Q1 2017, last date is 3/27, So on what basis I can find that which is full qtr?

Re: Pick the most recent Qtr

apthansh — Wed, 21 Jun 2017 18:26:15 GMT

Never mind on this.Thank you much.

Re: Pick the most recent Qtr

apthansh — Wed, 21 Jun 2017 18:26:52 GMT

Thank you.