Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joris_lansdaal
Creator
Creator

Help - Set-analysis and $ sign expansion.

Hi all,

I've got a challenge. I want to compare a selected period with the same period a year ago. In this case all the dates (Datum) between Jan2016 and May 2016 compared to Jan2015 and May 2015.

Delta Volume: sum(Volume)-Sum({$<Year=, Month=, Boekingsdatum=, Week=, MonthYear=, Datum={">=$(=AddMonths(monthstart( Min(Datum)),-12))<=$(=AddMonths( Max(Datum),-12))"}>} Volume).

Note: The same logic is applied to different expressions, but the set analysis is the same.

Picture1.jpg

The formula works perfectly until i make a selection in a field where there's only sales in april...

Picture2.jpg

Now the period evaluated is reduced by the selection, but i still want to compare Jan16-May 2016 to jan15-may16.

What is wrong in my formula?

4 Replies
joris_lansdaal
Creator
Creator
Author

Hi all,

at first I tought why is nobody able to help me out. I now release that I could have done a better job in describing my challenge. I've found a solution!

sunny_talwar

Would you be able to share what the solution was? Might be helpful for others in the future + I am curious what caused it

joris_lansdaal
Creator
Creator
Author

At first I used the field MonthYear to have the user select a certain period to analyse and compare to the same period last year. Selections can be made in other fields Like Channel ABND and BQS.

The challenge I faced was that certain combinations in Channel and BQS didn't have sales in all months. Like in the example only in sales in april16 occured. And instead of  comparing the sales for this combination in the period Jan16-May 2016 compared to jan15-may16, now Apr16 is compared to apr15.

I looked for an answer in GetSelectedField(MonthYear) but finaly made an alternative List Box MonthYear_Select and used below expression.


((Sum({<Year=, Month=, Week=, Datum=, MonthYear=,Datum={">=$(=AddMonths(Monthstart(Min(MonthYear_Select)),0))<=$(=AddMonths(MonthEnd(Max(MonthYear_Select)),0))"}>} Volume))/1000*VarFactor

-(Sum({<Year=, Month=, Week=, Datum=, MonthYear=,Datum={">=$(=AddMonths(Monthstart(Min(MonthYear_Select)),-12))<=$(=AddMonths(MonthEnd(Max(MonthYear_Select)),-12))"}>} Volume))/1000*VarFactor)

Knipsel1.PNG

sunny_talwar

Thanks for sharing this with all of us