Date range using YearQuarter

[BootCam]

Hi, 

I need to make a range using yearquarter like this:

2016-Q1  - 2016-Q2 if the users selects this way, other than that if they selects 2016-Q1 they will see the current selections and previous period 2015-Q1 numbers.

But when they selects the range as mentioned above like  this  2016-Q1   -  2016-Q2 , the numbers of this will show the current range selections and the previous period range numbers for 2015-Q1  -  2015-Q2.

Data:

Customer, QuarterYear, Sales
AD, 20151, 200
AD, 20152, 300
AD, 20153, 150
AD, 20154, 130
AD, 20161, 460
AD, 20162, 340
AD, 20163, 560
BD, 20151, 250
BD, 20152, 370
BD, 20153, 160
BD, 20154, 135
BD, 20161, 466
BD, 20162, 390
BD, 20163, 570
CD, 20151, 210
CD, 20152, 323
CD, 20153, 145
CD, 20154, 136
CD, 20161, 467
CD, 20162, 343
CD, 20163, 568

Thanks,

BootCam

[sunny_talwar]

Where are you looking to do this? In the script or front end?

And the requirement is that if the user select 2016-Q1 - 2016-Q2 , they see 2016-Q1 - 2016-Q2 and 2015-Q1 - 2015-Q2?

[BootCam]

Thanks Sunny!

I am trying to do it in the front end.

Best,

BootCam

[sunny_talwar]

And what exactly are you hoping to see? Can you explain it step by step?

[BootCam]

Hi Sunny,

Here it is:

1. I would like to have a QuarterYear list box where the user can select any YearQuarter and in the straight table they will see the value by this selection and also they will see the previous period value as well.

For example, If they select 2016-Q1 they will see the value of 2016-Q1 as the current period value and 2015-Q1 as the previous value.

2. If they select lets say multiple values like 2016-Q1 and 2016-Q2 at the same time they will see this as the current value but for the previous they will see the value for 2015-Q1 and 2015-Q2 value.

I know with this little data its kinda confusing but tried my best to explain.

Best,

BootCam

[sunny_talwar]

I think what you need is The As Of Table

Table:
LOAD *,
	 Date(MonthStart(Date#(Left(QuarterYear, 4) * 100 + Pick(Right(QuarterYear, 1), 3, 6, 9, 12), 'YYYYMM')), 'YYYYMM') as MonthYear;	 
LOAD * INLINE [
    Customer, QuarterYear, Sales
    AD, 20151, 200
    AD, 20152, 300
    AD, 20153, 150
    AD, 20154, 130
    AD, 20161, 460
    AD, 20162, 340
    AD, 20163, 560
    BD, 20151, 250
    BD, 20152, 370
    BD, 20153, 160
    BD, 20154, 135
    BD, 20161, 466
    BD, 20162, 390
    BD, 20163, 570
    CD, 20151, 210
    CD, 20152, 323
    CD, 20153, 145
    CD, 20154, 136
    CD, 20161, 467
    CD, 20162, 343
    CD, 20163, 568
];

AsOfTable:
LOAD MonthYear as AsOfMonthYear,
	 Year(MonthYear)*10 + Ceil(Month(MonthYear)/3) as AsOfQuarterYear,
	 MonthYear,
     'CY' as Flag
Resident Table;

Concatenate (AsOfTable)
LOAD MonthYear as AsOfMonthYear,
	 Year(MonthYear)*10 + Ceil(Month(MonthYear)/3) as AsOfQuarterYear,
	 AddYears(MonthYear, -1) as MonthYear,
     'PY' as Flag
Resident Table;

and then you can use this in your chart

Dimension

AsOfQuarterYear

Expressions

Current Year: Sum({<Flag = {'CY'}>}Sales)
Previous Year: Sum({<Flag = {'PY'}>}Sales)

[BootCam]

Hi Sunny,

 For some reason, i am getting the wrong numbers after selecting individual YearQuarter  or Range of YearQuarter Selections. Could you suggest?

Best,

BootCam

[sunny_talwar]

Not sure what you mean? What is wrong?

[BootCam]

Hi Sunny,

Please see the attachment for more elaboration.

Best,

BC

[sunny_talwar · Accepted solution]

Make your selection in AsOfQuarterYear instead of QuarterYear unless you have a reason to select QuarterYear? If there is, change the expressions to this

Sum({<Flag={'CY'}, QuarterYear, AsOfQuarterYear = p(QuarterYear)>}Sales)
Sum({<Flag={'PY'}, QuarterYear, AsOfQuarterYear = p(QuarterYear)>}Sales)