topic Re: Set analysis problem in QlikView

Set analysis problem

breukelaarj — Fri, 20 Jun 2014 07:32:49 GMT

Hi, I hope someone can tell me if there is a chance I can get my idea to work. Assume a table with 'ordernumber' and 'orderdate'. From the 'orderdate' a master calendar is created. So a simple calculation would be to count the 'ordernumber'. This way I can view the number of orders placed per timeframe. Assume I want selections on Year and Month. My Master calendar provides this. So I can view the number of orders per month, year combination. If I would also show in my table the number of orders of previous month I can do so by using a function in my expression for current date and calculate back a month. But that is sort of a static view in time, current month versus previous month. What I want is current selected period versus previous selected period. To start simple assume I have to select a month. So if I select March as month my next column would need to show the count of February. I need to use set analysis (assumption) and I need to exclude the month selection from the expression, but how can I use it anyhow to calculate the previous month? I have tried various examples but to no avail. I would be thrilled to have a working solution for above. I would be more thrilled if there was a way to determine the selected period. For example I only select year that it would show the count of the previous year. Hope someone can help me out. Johan Breukelaar

Re: Set analysis problem

tresB — Fri, 20 Jun 2014 07:38:14 GMT

No duplicate post please: Re: Set analysis with / without current selections

Re: Set analysis problem

alexandros17 — Fri, 20 Jun 2014 07:40:22 GMT

There is an easy way:

assume that your expression for current selected month is

Sum(Sales)

The expression for the previous month is

Sum({$ <month={'$(=getfieldselection(month)-1)'}>} Sales)

unfortunately this expression works from february because if you select january (1) then 1-1 is 0 and no selection will be made for previous period.

Another way could be using the script,

Suppose you have

Calendar:

Load

...

month,

year

...

resident myCalendar

Then add the field

Load

...

month,

Addmonth(makedate(year, month,1), -1) as prevMonth

...

resident myCalendar

Let me know

Re: Set analysis problem

ashfaq_haseeb — Fri, 20 Jun 2014 07:42:57 GMT

Hi,

start with attached url to have a bigger picture.

Regards

ASHFAQ