14 Replies Latest reply: Oct 24, 2015 9:03 AM by Kumar Pramod

# Week number or Quarter number

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

• ###### Re: Week number or Quarter number

Use something like below..

```Data:
[
Date
01/01/2012
15/02/2015
];

Temp:
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:
\$(vMinDate) + IterNo() - 1 as TempDate
AutoGenerate 1
While \$(vMinDate) + IterNo() - 1 <= \$(vMaxDate);

Calendar:
*,
'Q'&AutoNumber(TempQuarter) as Quarter;
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;
```
• ###### Re: Week number or Quarter number

i'm getting error in the first line only.

• ###### Re: Week number or Quarter number

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.

• ###### Re: Week number or Quarter number

No, I changed the field names.

• ###### Re: Week number or Quarter number

HI Manish,

Please find the .qvf

• ###### Re: Week number or Quarter number

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

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

Data:

CanonicalDate

01/01/2012

15/02/2015

];

This table I have given only for Demo purpose..

• ###### Re: Week number or Quarter number

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.

• ###### Re: Week number or Quarter number

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.

• ###### Re: Week number or Quarter number

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

• ###### Re: Week number or Quarter number

cant help you further until I will get the fact table which you are using in excel file...

• ###### Re: Week number or Quarter number

OK i will add excel, din't get what is fact table. i need to add all the tables what i have used?

• ###### Re: Week number or Quarter number

I think that's because the calendar table and the table with Month_Year are not linked properly.

Maybe all you need is adding a common key to the calendar:

1. Calendar: