topic Set analysis range in QlikView

Set analysis range

Wed, 24 Apr 2013 15:08:03 GMT

Dear all,

I have a question regarding the use of date ranges in set analysis.

When using the following syntax I'm getting 4 results as return

Serial	Date	Price
A	01/01/2013	10000
B	16/12/2012	9000
C	10/10/2012	9500
D	30/10/2012	8000

sum({$<MonthYearNum ={"<= $(=Max(MonthYearNum))"},MachineStatus = {'Consigment','Demonstration','Free', 'Ordered', 'Purchasing', 'Reserved'}

,Year=,Month=,Quarter= >}MachinePurchaseCost)

MonthYearNum will return 201301 as a number.

What I wanted to achieve is to have a date range for example every serial which has a date between the month/year I've selected. (eg: Jan/2013). When I choose this, I'm counting back 2 months. So I will get All serials between November 2012 and January 2013.

This is giving me 201211: Year(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)) & Num(Month(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)),'00')

However, when I integrate these two syntaxes I'm getting nothing as result. My end result should be Serial A and B.

sum({$<MonthYearNum ={"<= $(=Max(MonthYearNum)) >= Year(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)) & Num(Month(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)),'00')"},MachineStatus = {'Consigment','Demonstration','Free', 'Ordered', 'Purchasing', 'Reserved'}

,Year=,Month=,Quarter= >}MachinePurchaseCost)

Am I converting something wrong?

I appreciate the help.

Kind regards

Re: Set analysis range

Wed, 24 Apr 2013 19:13:13 GMT

I'm not entirely sure why that expression isn't working - maybe it's because the & converts things into a string, which isn't being read as a number? If so you could try 100*year + Month and see if that helped.

However, it might be easier to go for the expression:

Date={">=$(=num(addmonths(vMonthYear, -2))) <=$(vMonthSelected)"}, I.E. Why use MonthYearNum if you don't have to?

Re: Set analysis range

fosuzuki — Wed, 24 Apr 2013 20:50:44 GMT

I think you should've included the second part inside a dollar-sign expression:

sum({$<MonthYearNum ={"<= $(=Max(MonthYearNum)) >= $(=Year(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)) & Num(Month(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)),'00'))"},MachineStatus = {'Consigment','Demonstration','Free', 'Ordered', 'Purchasing', 'Reserved'}

,Year=,Month=,Quarter= >}MachinePurchaseCost)

Re: Set analysis range

Thu, 25 Apr 2013 06:38:50 GMT

Not completely sure why it didn't work when I tried it. I probably made a typo mistake in my expression but this did the trick.

Thanks!

Re: Set analysis range

Thu, 25 Apr 2013 07:08:22 GMT

I've been using MonthYearNum quite a bit. But it's indeed easier to use the expression you said. Both expressions give me the correct results.

Thanks for the hint though. This makes my expression shorter and easier to understand.