Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have attached trading calendar..
on the basis of this calendar How can I get Quarter To Date, Month To Date and Year To Date Sale?
Please help me out.
Thanks,
try like this
Thanks for immediate response..
I had tried it...
there are Yearstart() and more functions which work for standard claendar..
for example for any date, suppose today(), yearstart() gives value 01/01/2015, but according to my Trading calendar year start is from 03/03/2015.
This is the problem..
in sample file no sasles data . can you upload with that to check
Hi Arul,
Sales data Table is very large in size, There are two fields useful for this 'DateKey' and 'Amount'.
I have used following script to create qvd:
TradingCalendarNEW:
LOAD "DateKey","DayName","Fiscal Week","Fiscal Year",FiscalQuarter,
if(FiscalQuarter = 'Q1',1 ,if( FiscalQuarter = 'Q2', 2, if(FiscalQuarter = 'Q3',3, if(FiscalQuarter = 'Q4', 4)))) as FiscalQuarterNum,
Year(TempDate) AS CalendarYear,
Month(TempDate) AS CalendarMonth
;
SQL SELECT Date as 'DateKey',Date as TempDate,
DayName,
[Fiscal Week],
Quarter as FiscalQuarter,
Year as 'Fiscal Year'
FROM QlikViewDB.dbo.TradingCalendar;
STORE TradingCalendarNEW into TradingCalendarNEW.qvd (qvd);
Drop Table TradingCalendarNEW;
and following expression to calculate Sale this quarter,
sum({$<FiscalYearNum= {$(=max(FiscalYearNum))}, FiscalQuarter=, DateNum={">=$(=NUM(Min(DateNum)))<=$(=Max(DateNum))"} >} Amount)/1000
I am facing problem to find Sale last Quarter,
Thanks,
Hi
As far as I understood from your query, you want FiscalYear start from 01-March to 28/29 Feb. Please correct me if its wrong assumption.
You can calculate as follows:
YTD is 01-01-2014 to Selected Date
Expression would be
Sum({<Year=, Month=, Quarter=, Week=, DateField=,
DateNum={">=$(=Num(YearStart(Max(DateNum), 0, 3 ))<=$(=Max(DateNum))"}>} Sales)
QTD- Quarter to Date
In the place of year use Quarter
Sum({<Year=, Month=, Quarter=, Week=, DateField=,
DateNum={">=$(=Num(QuarterStart(Max(DateNum), 0, 3 ))<=$(=Max(DateNum))"}>} Sales)
MTD- Month to Date
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)
WTD- Month to Date
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)
Or you can also Create a FiscalMapping as shown below (Inline / call Excel) and map with your Calander & use FiscalMonthNumber/FiscalQtr.
ActualMonthNo | ActualMonth | FiscalMonthNo | FiscalQtr |
1 | January | 11 | Q4 |
2 | February | 12 | Q4 |
3 | March | 1 | Q1 |
4 | April | 2 | Q1 |
5 | May | 3 | Q1 |
6 | June | 4 | Q2 |
7 | July | 5 | Q2 |
8 | August | 6 | Q2 |
9 | September | 7 | Q3 |
10 | October | 8 | Q3 |
11 | November | 9 | Q3 |
12 | December | 10 | Q4 |
For FiscalYear, you can use - YearName(DateNum,0,3) as FiscalYear
Let me know if it helps you to resolve your problem or anything else I can help you more.
--
Regards,
Prashant P Baste.
PFA,
In this case you can map Fiscal calender with your internal date & Makedate for defining ranges....
For example : (ActualMonthNumber = > FiscalMonthNumber ) : (1 => 11), (2 => 12), (3 => 1) , (4 => 2).... (12 => 10)
and yearstart as YearStart(DateFieldName,0.2) as FiscalYear
Use this FiscalMonthNumber & FiscalYear in calculations.
--
Regards,
Prashant P Baste.
Thank you so much for new approach,
Actually Fiscal year start is not depends upon 1st day of month, it takes 1st sunday of Mar-Apr,
in my case here Fiscal Year start from
30/03/2014 | Sun | 1 | Q1 | 2014-15 |
it is different for each fiscal year,
because of this quarterstart, yearstart are not giving correct value.
Mapping can work.. Can you more explain How should I use mapping in this case.
Thanks Again
Actually I used FiscalQuarterNum= {$(=max(FiscalQuarterNum)-1)}
it works but it takes all sale of last quarter but I need Quarter To Date,
Do you have solution of this?
Thanks,