Using parallelPeriod for calculations with date filter

[makarand]

Hi,

my requirement is I want to filter the period  (say 01-31 May 2016 / using slider maybe)

and see the sales for this year as well as last year for the same period.

the period could be dates/months/years but I want to see this year value as well as last year value.

I tried parellel period and relative period but both disappears when i put period less than a year.

illustative diagram is attached.

hope this is possible wo changing dataset.

Scenario >

01may2016 to 31 May 2016 >> 01-31 may 2016 and 2015

01jan2015 to 31dec2015 >> 010115 to 31122015 and 01012014 to 31122014

[BrunoMueller]

Hi

You need to create a filter on your report object, the filter can use any parameter value. Please note a parameter can only be numeric or character.

So one approach could be:

Create a new calculated date item like below. This will create a character item in form of 2016M05.

Format('date'n[Raw], 'YYMM7.')

Then put a dropdown list into the canvas area and assign this the calculated item from before to this dropdown list.

Create a parameter of type character and assign it to the dropdown list.

then create a filter on you bar chart similar to this:

( Year('date'n) = ( Parse(Substring('dateYYMM_p'p, 1, 4), 'F12.') - 1 ) )
OR
( Year('date'n) = Parse(Substring('dateYYMM_p'p, 1, 4), 'F12.') )

Now whenever to select a given month from the dropdown list, the bar chart will be filtered accordingly.

The example only covers filter for the selected year and year - 1, it needs to be adapted to also check for the month.

You then have data from two years and can use Periodic operators

Bruno

[makarand]

Thanks Bruno..
Will check as I want to give range for minimum 10 days diff and max year..

[makarand]

Hi Bruno,
Above method u suggested create only one bar for given month and not two bars for different year seperately as needed. +
I want to select both starttime and endtime which is not possible here.
Plz suggest alternatives as I'm not proficient with t using parameters

[BrunoMueller]

Hi

If you want to select a date range, then I suggest to use a slider in the section prompt area. Assign the right date variable to the slider, then your report objects are filtered automatically

See a screenshot below:

Bruno

[makarand]

i have created a filter like attached file  or text

( ( Year(DatePart('INVOICE_DATE'n)) > ( Parse(Substring('start_date'p, 5, 4), 'F12.') - 1 ) ) AND ( Year(DatePart('INVOICE_DATE'n)) <= ( Parse(Substring('end_date'p, 5, 4), 'F12.') - 1 ) ) ) OR ( ( Year(DatePart('INVOICE_DATE'n)) > Parse(Substring('start_date'p, 5, 4), 'F12.') ) AND ( Year(DatePart('INVOICE_DATE'n)) <= Parse(Substring('end_date'p, 5, 4), 'F12.') ) )

 but still I am not able to generate the mentioned report.

will appriciate if you correct the code for the ranges.

The Slider will only accept numeric parameters and not text parameters, hence not able to parameterize to the dates like u suggested it for text parameters

( ( Year(DatePart('INVOICE_DATE'n)) > ( Parse(Substring('start_date'p, 5, 4), 'F12.') - 1 ) ) AND ( Year(DatePart('INVOICE_DATE'n)) <= ( Parse(Substring('end_date'p, 5, 4), 'F12.') - 1 ) ) ) OR ( ( Year(DatePart('INVOICE_DATE'n)) > Parse(Substring('start_date'p, 5, 4), 'F12.') ) AND ( Year(DatePart('INVOICE_DATE'n)) <= Parse(Substring('end_date'p, 5, 4), 'F12.') ) )

[BrunoMueller]

Hi

I do not know what the content of the start_date parameter looks like, so difficult to say

Also is invoice_date really a datetime value? If it is just a date, you do not need the datepart operator?

to test the outcome of your expressions, I suggest to create a new data item and then use the preview button to see the outcome of an expression, see also below. This should help with debugging.

Bruno

[makarand]

Thnks Bruno,

I created filter which will include date values for this year and last year as u suggested but with the Date9. format and then use datefromMDY to create required range/period.

Thnks again for your help.