Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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$);
Are we picking 4/3/2017 to 6/10/2017 because it is the current quarter?
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
If the above is true, then may be this
=Sum({<[Entry Date] = {"$(='>=' & Date(QuarterStart(Today())) & '<=' & Date(QuarterStart(Today(), 1)-1))"}>}[Expense in USD]) * 4
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
This is for the currebt Qtr right ?
I am looking for the most Recent Qtr...
Thanks as always.
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
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$);
Hi,
without change script, may be
Sum({<[Entry Date]={'>=$(=QuarterStart(Max([Entry Date])))<=$(=QuarterEnd(Max([Entry Date])))'}>} [Expense in USD])
Regards,
Antonio
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.