Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
How should i calculate quartes from given date.I have tried but it's giving me quarter for each month but I want quarters according to fiscal month
Try like:
Load *,
'Q' & Celi(fMonth/3) as FiscalQtr;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
Load Year(Date) as Year, // Your standard master calendar
Month(Date) as Month
From <>;
Check enclosed file...
Try like:
Load *,
'Q' & Celi(fMonth/3) as FiscalQtr;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
Load Year(Date) as Year, // Your standard master calendar
Month(Date) as Month
From <>;
Can you just share script as I am unable to open your qvw?
Try this:
Load *,
'Q' & Ceil(Month/3) as Quarter
Cheers,
Lucas
Sales:
Load * Inline
[
InvoiceDate,Sales
01/01/2013, 100
20/01/2013, 200
01/02/2013, 300
28/03/2013, 400
03/04/2013, 150
04/05/2013, 250
05/05/2013, 300
06/06/2013, 100
06/07/2013, 120
07/07/2013, 180
08/08/2013, 60
09/09/2013, 80
10/10/2013, 280
01/11/2013, 300
01/12/2013, 165
10/01/2014, 140
15/01/2014, 160
20/02/2014, 190
];
MinMaxDate:
Load
Min(InvoiceDate) as MinDate,
Max(InvoiceDate) as MaxDate
Resident Sales;
Let vMinDate = NUM(Peek('MinDate',0,'MinMaxDate'));
Let vMaxDate = NUM(Peek('MaxDate',0,'MinMaxDate'));
Drop Table MinMaxDate;
TempDate:
Load
Date($(vMinDate) + RowNo() - 1) as InvoiceTempDate
AutoGenerate $(vMaxDate) - $(vMinDate)+1;
InvoiceMasterCalendar:
LOAD
InvoiceTempDate as [InvoiceDate],
Day(InvoiceTempDate) as InvoiceDay,
WeekDay (InvoiceTempDate) as InvoiceWeekday,
Week(InvoiceTempDate) as InvoiceWeek,
Month(InvoiceTempDate) as InvoiceMonth,
Num(Month(InvoiceTempDate)) as InvoiceMonthNO,
Year(InvoiceTempDate) as InvoiceYear,
'Q' & ceil(month(InvoiceTempDate) / 3) as InvoiceQuarter,
Date (monthstart (InvoiceTempDate) , 'MMM YYYY') as InvoiceMonthYear,
if(num(month(InvoiceTempDate))<=3,Year(InvoiceTempDate)-1&'-'&right(Year(InvoiceTempDate),2),Year(InvoiceTempDate)&'-'&right(year(InvoiceTempDate)+1,2)) as FinancialYear,
if(num(month(InvoiceTempDate))<=3,Year(InvoiceTempDate),year(InvoiceTempDate)+1) as FinancialYear1,
if(num(month(InvoiceTempDate))>=1 and num(month(InvoiceTempDate))<=3,'Q4',
if(num(month(InvoiceTempDate))>=4 and num(month(InvoiceTempDate))<=6,'Q1',
if(num(month(InvoiceTempDate))>=7 and num(month(InvoiceTempDate))<=9,'Q2',
if(num(month(InvoiceTempDate))>=10 and num(month(InvoiceTempDate))<=12,'Q3')))) as FinancialQuarter
RESIDENT TempDate
ORDER BY InvoiceTempDate ASC;
DROP TABLE TempDate;
Try this code in script here change TempDate with your Date
'Q'&If(Month(QuarterStart(TempDate,4))='1' ,4,Div(Month(QuarterStart(TempDate,4)),3)) as QuarterStartsapr,