Week number or Quarter number

[#1]

Hi all,

I have a date from 2012 in my database. I have created a master calendar.

The week number and quarter is same for all the four years(2012,2013,2014,2015).

Example:  January month of all the 4 years belongs to Quarter Q1. I don't need like this i need as jan-2012 in Q1, jan-2013 in Q5, jan-2014 in Q9, jan -2015 in Q13.

Because i need to show data in quarter for all the 3 years in line graph.

or anyone explain how can i achieve this?

You can see in above image that the data is comparing for every four months i need to prepare a line graph like above.

Regards,

Pramod

[MK_QSL]

Use something like below..

Data:
Load * Inline
[
  Date
  01/01/2012
  15/02/2015
];


Temp:
Load 
  Min(Date) as MinDate, 
  YearEnd(Max(Date)) as MaxDate
Resident Data;


Let vMinDate = Num(Peek('MinDate',0,'Temp'));
Let vMaxDate = Num(Peek('MaxDate',0,'Temp'));


Drop Table Temp;


Temp_Calendar:
Load 
  $(vMinDate) + IterNo() - 1 as TempDate
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);


Calendar:
Load 
  *,
  'Q'&AutoNumber(TempQuarter) as Quarter;
Load 
  Date(TempDate) as Date,
  Month(TempDate) as Month,
  Year(TempDate)& Num(Ceil(Month(TempDate)/3),'00') as TempQuarter,
  Year(TempDate) as Year
Resident Temp_Calendar
Order By TempDate;


Drop Field TempQuarter;


Drop Table Temp_Calendar;

[#3]

i'm getting error in the first line only.

[MK_QSL]

Kumar, the example I have provided is for sample purpose. Obviously you have to change according to your TableName and FieldName. If you are just going to copy paste my code in your script, you would get errors.

[#5]

No, I changed the field names.

[#6]

HI Manish,

Please find the .qvf

[MK_QSL]

I can see that you have used my Code without change...

You have to use TableName and FieldName according to your Database...!

Why you are loading this?

Data: 

Load * Inline 

[ 

  CanonicalDate 

  01/01/2012 

  15/02/2015 

]; 

This table I have given only for Demo purpose..

[#8]

sorry now i changed the script please check it once. i think this is also not fine.

I am not getting exactly what you telling to do.

[MK_QSL]

This could be because the Variable Set Date and your date both are having different format.

Set the System date format same as your Date from fact table.

What is the exact error you are getting? I can't load the data at my end due to the fact table is coming from database.

[#10]

Data is loaded but for all the months it is showing all the quarter.

you can see in the image from jul 2012 to jun 2015 it will show Q1 and then again for all the months Q2 like that