Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
breukelaarj
Partner - Contributor
Partner - Contributor

Set analysis problem

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

3 Replies
tresesco
MVP
MVP

alexandros17
Partner - Champion III
Partner - Champion III

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

ashfaq_haseeb
Champion III
Champion III

Hi,

start with attached url to have a bigger picture.

Regards

ASHFAQ