Set analysis parameter P

[Anonymous]

Hi Folks,

I am struggeling with the following problem:

I have requests which are delivered every month. new requests come in each month, while other requests were solved and does not appear any more.

Now I am looking for a solution to count the number of new and solved requests. I donot want to do it in script because there are a lot of fields that should be calculated.

With this formula (date is hardcoded) it works fine. Even if I try arbitrary dates it works perfect

=count({<Request=p({<Datefield={'01.01.2015'}>})
-p({<Datefield={'01.12.2014'}>})>} Request)

The Problem starts when I want to calculate dynamically

=count({<Request=p({<Datefield={'=AddMonths(Berichtsdatum,0)'}>})
-p({$<Datefield={=AddMonths(Berichtsdatum,-1'}>})>} Request)

It does not matter whether I use admdonths(field,0) or any other number to change the month!

I have a table with unique Datefield in each row (for each month), so I would assume that the forumla works.

As you see from the Image the column (Diff Jan-D) Shows the correct value (as 155 requests are common in both months and I have only 2 months right now)

The two columns from right contain 1 or 2 addmonths, it shows 0! (I even tried date(addmonths(field,1),'DD.MM.YYYY')

Any ideas?

[luciancotea]

Set Analysis is not calculated dynamically. It is calculated once, globally.

[Gysbert_Wassenaar]

If you select only one value in the Berichtdatum field it will work. That's because you don't use an aggregation function on it and that's why Qlikview applies the default only() function. Like this:

=count({<Request=p({<Datefield={'=AddMonths(only(Berichtsdatum),0)'}>})-p({$<Datefield={=AddMonths(only(Berichtsdatum),-1'}>})>} Request)

You could try using another aggregation function like max or min so you can select more values in the Berichtdatum field.

However, you should keep in mind that the set is calculated at the chart level, not at the row level. So if you're using Berichtdatum or another related period field (month, quarter, year) as a dimension in your chart then you will get incorrect results.

[Anonymous]

if I use only or max it doesnot change anything.

If I select only 1 month it does not change anything

when set Analysis is performed?

with the following formula I get the exact number of the month, but not from the following month.

I can change the 1 (in addmonths) to any other number, the result is always the same

=count({<request=p({$<Berichtsdatum={'=addmonths(only(Berichtsdatum),1)'}>}request)>} request)

Do you have another idea how I can get the correct numbers or is it not possible on the surface (and I need to calculate it in script which is not that flexible)

[Gysbert_Wassenaar]

Like I said:

However, you should keep in mind that the set is calculated at the chart level, not at the row level. So if you're using Berichtdatum or another related period field (month, quarter, year) as a dimension in your chart then you will get incorrect results.

Perhaps you can use an AsOf table that links each month with the previous month(s). That's a common method to get around the issue of calculating over different periods while using a period dimension in your chart. See for example this document: Calculating rolling n-period totals, averages or other aggregations