topic Re: Problem in working with dates in QlikView

Problem in working with dates

saurabh_karlewar — Wed, 05 Feb 2014 13:44:09 GMT

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

Re: Problem in working with dates

Gysbert_Wassenaar — Wed, 05 Feb 2014 13:49:06 GMT

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 ... ;

Re: Problem in working with dates

jfkinspari — Wed, 05 Feb 2014 13:49:11 GMT

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.

Re: Problem in working with dates

Colin-Albert — Wed, 05 Feb 2014 13:51:24 GMT

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]

Re: Problem in working with dates

saurabh_karlewar — Wed, 05 Feb 2014 13:52:52 GMT

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.

Re: Problem in working with dates

Colin-Albert — Wed, 05 Feb 2014 13:54:51 GMT

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.