Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Creator
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

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:

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$);

View solution in original post

15 Replies
Highlighted

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

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

Capture.PNG

Highlighted
Specialist
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
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.

sunnystalwar1

Highlighted

Re: Pick the most recent Qtr

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

Highlighted

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:

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$);

View solution in original post

Highlighted
Master III
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
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.

sunnystalwar1kush141087