Problem in working with dates

[saurabh_karlewar]

Hi,

I am developing a sales application.

My problem statement is as follows:

The opportunities have est. close date. From this date I've create two new fields called Closure Qtr and the Closure Year. Now the problem is, if I select year 13-14 and 14-15 and Qtr as Q1 and Q4, it will show me the data for Q1 13-14, Q4 13-14, Q1 14-15 and Q4 14-15.

However my actual requirement is I want to see data only for Q4 13-14 and Q1 14-15.

Can someone please explain me how to handle these kind of situations?

Thanks

[Gysbert_Wassenaar · Accepted solution]

Create a field QuarterYear too and make your selection in that field. You can create that by concatenating the Closure Qtr with the Closure Year. For example

MyTable:

LOAD *, [Closure Qtr] & '-' & [Closure Year] as [Closure Quarter-Year];

LOAD *, ... as [Closure Qtr], ... as [Closure Year] FROM ... ;

[jfkinspari]

You could make a single field containing both the year and Qtr.

That way you can select the exact periods - instead of the combinations of Year and Qtr.

[Colin-Albert]

You will need a new dimension that concatenates the Year and Qtr

so you have 13-14 Q1, 13-14 Q2, 13-14 Q3, 13-14 Q4, 14-15 Q1, etc.

Then you can select 13-14 Q4 and 14-15 Q1

Add this in your load script

     [Closure Year] & ' ' & [Closure Qtr] as [Closure YearQtr]

[saurabh_karlewar]

Earlier I used the similar approach, but the problem occurs when the data goes beyond 3 years as every year will have 4 entries for each quarter.

[Colin-Albert]

You will need a list box that will have 4 entries for every year to allow you to select the rolling quarter from one year to the next.