Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
agustapia
Contributor III
Contributor III

Set analysis date range from last year

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!!!!

1 Solution

Accepted Solutions
agustapia
Contributor III
Contributor III
Author

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.

View solution in original post

7 Replies
andrey_krylov
Specialist
Specialist

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

MK9885
Master II
Master II

Did you try

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


Month field is Jan, Feb, Mar etc

Year field is 2017,2018 etc..

agustapia
Contributor III
Contributor III
Author

Thanks, but not working neither...

agustapia
Contributor III
Contributor III
Author

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.

andrey_krylov
Specialist
Specialist

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.


agustapia
Contributor III
Contributor III
Author

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.

agustapia
Contributor III
Contributor III
Author

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