Pick the most recent Qtr

[apthansh]

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.

[sunny_talwar]

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

[Anil_Babu_Samineni]

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

[sunny_talwar]

If the above is true, then may be this

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

[prat1507]

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

[apthansh]

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‌

[Kushal_Chawda]

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

[sunny_talwar · Accepted solution]

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

[antoniotiman]

Hi,

without change script, may be

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

Regards,

Antonio

[apthansh]

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‌