topic Help - Set-analysis and $ sign expansion. in QlikView

Help - Set-analysis and $ sign expansion.

joris_lansdaal — Tue, 24 May 2016 06:45:30 GMT

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.

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

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?

Re: Help - Set-analysis and $ sign expansion.

joris_lansdaal — Thu, 26 May 2016 20:28:33 GMT

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!

Re: Help - Set-analysis and $ sign expansion.

sunny_talwar — Thu, 26 May 2016 20:33:34 GMT

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

Re: Help - Set-analysis and $ sign expansion.

joris_lansdaal — Thu, 26 May 2016 21:18:17 GMT

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)

Re: Help - Set-analysis and $ sign expansion.

sunny_talwar — Thu, 26 May 2016 22:29:16 GMT

Thanks for sharing this with all of us