Variable in where clause

[daniel_f]

Hi All,

I'm trying to load a table with filter in Where clause position and i need your help

how can i load only quarter 2016-Q3 if this is my script

The current "Quarter_Rank" is 38 and i need to load next Next_Quarter_Rank

Table:

LOAD

     [Product 1],

     [Product 2],

     [Total Qty],

     [Year Quarter],                    // format: '2016-Q2'  (list of quarters from 2013-Q1 to 2017-Q4)

     Quarter_Rank,                   // numbers 1 to 100, each quarter have a number

     Next_Quarter_Rank,          // is 39 (number)

     Actual_Quarter_Rank        // is 38 (number)

FROM

[..\QVD\Test\FC_Quarter.qvd]

(qvd)

Where  Quarter_Rank = / * i need a variable here to load Quarter_Rank= 39  * / ;

i tried this kind:

Where  Next_Quarter_Rank = Actual_Quarter_Rank + 1 but doesn't work (load empty)

can someone advise the right method?

Thanks in advance,

Daniel

[daniel_f]

Can this file help?

[daniel_f]

Hi Satya,

thank for your help

You assuming right, each quarter have only one rank.

well, the script is working but i don't understand what the limitation is when i reload the script.

additional, the formula is not generic. for example "year(Date(now()))" will not be valid when i'll be on 2016-Q4

any other idea

[Anil_Babu_Samineni]

Daniel,

How about Dense_Rank() in DB?

[Anonymous]

My calculation is generic:

I tried it with the following two dates and it worked perfectly giving 2017-Q1

LET YearQuarter_1 = Year(AddMonths(Date('10/01/2016'),4))&'-Q'&Ceil(Month(AddMonths(Date('10/01/2016'),4))/4);

LET YearQuarter_2 = Year(AddMonths(Date('12/31/2016'),4))&'-Q'&Ceil(Month(AddMonths(Date('12/31/2016'),4))/4);

trace $(YearQuarter_1)   $(YearQuarter_2);

[daniel_f]

can you please explain?