topic Set analysis, Intersection between month and Month-1 in QlikView

Set analysis, Intersection between month and Month-1

Wed, 23 Oct 2013 08:54:57 GMT

Hi everyone,

I'm stuck in this problem since one day and i don't find any solution...

I want to count old opportunities ("Anciennes") which still exist in month dimension AND in month-1. For exemple, for September, i want to count opportunites which exist in September and "Août" (August).

It works when i select one month and one Year, but not when i select only one Year

I think it's an Set Analysis with an Intersection operation. So i try:

count (distinct {<OpportunityCD = P ({<Year={$(=Only(Year(PRJ_ReloaDate)))}>} OpportunityCD ) >*<OpportunityCD = P ({<Year={$(=Only(Year(PRJ_ReloaDate)-1))}>} OpportunityCD ) > } OpportunityCD )

And

Count({$<
OpportunityCD = p(
{<Month={$(=Only(Month(MonthName(PRJ_ReloaDate))))}, Year={$(=Only(Year(PRJ_ReloaDate)))} >}
)
*
p(
{1<Month={$(=only(Month(AddMonths(PRJ_ReloaDate,-1))))}, Year={$(=only(Year(AddMonths(PRJ_ReloaDate,-1))))} >})

>} Distinct OpportunityCD)

But it still give me wrong number or 0.

Maybe i'm on right way, maybe, absolutly not...

Please, Help-me !

Thanks !

Quentin.

Re: Set analysis, Intersection between month and Month-1

bbi_mba_76 — Wed, 23 Oct 2013 09:22:47 GMT

Hi,

set analysis could be used to "ignore/force" selections on fields.

In your case it is possible to work on the script to add the previous month value or with inter record funcionts (like above)

Re: Set analysis, Intersection between month and Month-1

Gysbert_Wassenaar — Wed, 23 Oct 2013 09:25:53 GMT

Your problem is that you're using month as dimension. The set analysis expression doesn't create a set per row, but per chart. It can't look at other rows, so the row for september can't calculate anything from august. That's why it works if you select a month and a year, but not if you select only a year.

What you could try is to create a new field in the script that retrieves the opportunities of the previous month.

Or a kind of AsOf table that links each month with itself and with the previous month.

AsOf:

Month_AsOf, Month, Period

janv. , janv. , Current

févr., févr., Current

févr., janv., Previous

mars, mars, Current

mars, févr, Previous

...etc

You can then use Month_AsOf as dimension instead and expressions like:

count({<Period={'Current'},OpportunityCD=P({<Period={'Previous'}>}OpportunityCD)>}distinct OpportunityCD)