topic Set analysis date range from last year in App Development

Set analysis date range from last year

agustapia — Thu, 01 Nov 2018 22:10:54 GMT

Hi all,

I am using the Sense Date Range Picker to select the sum of sales from an specific date range, and it works.

However I was asked to add last year comparisson (ie, if I am seeing sum of sales from October 15 to October 21, 2018, then they want the sum of the sales from October 15 to October 21, 2017 in the next column).

I have tried set analysis and it seems to work when i am not using the date range picker, however when I select a date range it doesn't work.

What I have done is:

1. Declare two variables:

- vMaxSelect =Num(AddYears(Max(Fecha), -1))

- vMinSelect =Num(AddYears(Min(Fecha), -1))

2. Use this formula in a straight table:

=Sum({<Fecha = {">=$(vMinSelect)<=$(vMaxSelect)"}>} $(vVenta))

3. Associate dimension "Fecha" to date range picker and select a date range.

Please help!!!!

Re: Set analysis date range from last year

andrey_krylov — Fri, 02 Nov 2018 03:52:10 GMT

Hi Agustin. Most likely, it is necessary to ignore selections in the field made by the date range picker, in other words in all period fields except "Fecha".

Re: Set analysis date range from last year

MK9885 — Fri, 02 Nov 2018 11:47:37 GMT

Did you try

Sum({<Month = {"<=$(=num(Month))"}, Year = { $(=(Year) - 1)}>} $(vVenta))

Month field is Jan, Feb, Mar etc

Year field is 2017,2018 etc..

Re: Set analysis date range from last year

agustapia — Mon, 05 Nov 2018 16:59:17 GMT

Thanks, but not working neither...

Re: Set analysis date range from last year

agustapia — Mon, 05 Nov 2018 17:01:11 GMT

Hi Andrey, I did made a dummy dimension of date (same data, different name) from data load editor but it didn't work. Can you please be more specific (maybe a little example of what should I do)? Thanks.

Re: Set analysis date range from last year

andrey_krylov — Mon, 05 Nov 2018 19:29:03 GMT

Hi Agustin. As you said above your expression works fine while you are not using the date range picker, so the only thing is to be done is ignoring other selections. If there is a master calendar I would add all of its fields to set analysis

=Sum({<Fecha = {">=$(vMinSelect)<=$(vMaxSelect) , Month, Year, PeriodField1, PeriodField2,.. "}>} $(vVenta))

or at least that used by the date picker as well as those in charts, listboxes...

When you set a period in one field using set analysis it does not ignore selections in other date fields and you've to overide them in set analysis as well.

Re: Set analysis date range from last year

agustapia — Mon, 05 Nov 2018 21:17:04 GMT

Hi Andrey,

This was a tricky one, as your solution did not work because Fecha is the variable I need to ignore and is also the one over which the selection is made, so a simple set analysis without ignoring any other variable should have been enough, but it wasn't. Also adding Fecha in the end of the set analysis to ignore it didn't make it.

The solution came by adding a 1 before set analysis formula to ignore all filters, I still don't understand why my original formula is not working, but at least now I have a solution to the issue I had.

Really appreciate your help.

Re: Set analysis date range from last year

agustapia — Fri, 09 Nov 2018 15:38:44 GMT

Hi Andrey,

Just wanted to get back to you to let you know that, for some reasons I realized that Date Range Picker was not making a selection over my Fecha field, but over a transformation of it that showed the correct time stamp for the Picker to work. Then when I did the transformations in the data load editor for my field Fecha to be used in the Date Range Picker without transformations, the set analysis worked without any of the tricks we were talking about.

No doubt that every day we can learn something new...

Thanks for the support...