# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for
Did you mean:
Highlighted
Creator

## Pick the most recent Qtr

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.

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Pick the most recent Qtr

May be this

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

Where Quarter is created in the script like this

Table:

[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

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

15 Replies
Highlighted
MVP

## Re: Pick the most recent Qtr

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

Highlighted

## Re: Pick the most recent Qtr

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

Or

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP

## Re: Pick the most recent Qtr

If the above is true, then may be this

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

Highlighted
Specialist

## Re: Pick the most recent Qtr

Hi

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

Highlighted
Creator

## Re: Pick the most recent Qtr

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 1st 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.

Highlighted
MVP

## Re: Pick the most recent Qtr

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

Data:

[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

Highlighted
MVP

## Re: Pick the most recent Qtr

May be this

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

Where Quarter is created in the script like this

Table:

[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

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

Highlighted
Master III

## Re: Pick the most recent Qtr

Hi,

without change script, may be

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

Regards,

Antonio

Highlighted
Creator

## Re: Pick the most recent Qtr

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.