Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

In set-analysis, how to I get the data for one YearMonth-period prior to first period in my selection?

I have a calendar in my qvw that gives me a field called PeriodCounterCurrent. It starts at 1 and counts every month since 1990-01. In a text object I have the following code,

= COUNT ( {$ <      PeriodCounterCurrent = {$(#=MIN(PeriodCounterCurrent)) }     > } DISTINCT EmployeeNumber)

that counts the total employees in my selection. It works fine.

What I need is to push the condition back one period, to get the number of employees for the period before my first period in the selection. Let's say that I have a selection of jan-mar 2012 (201201-201203) which in my case corresponds to 265-267 in PeriodCounterCurrent. I need to know the number of employees for PeriodCounterCurrent = 264.

I have tried the following,

= COUNT ( {$ <      PeriodCounterCurrent = {$(#=MIN(PeriodCounterCurrent) -1 ) }     > } DISTINCT EmployeeNumber)

but that only gives me 0 as a result.

I'm new to QV, and I use the documentation a lot. What I base my code on it the following,

sum(  {$<Year = {$(#=Only(Year)-1)}>} Sales )

taken from the reference guide, page 812.

What am I doing wrong?

1 Solution

Accepted Solutions
Not applicable

Re: In set-analysis, how to I get the data for one YearMonth-period prior to first period in my selection?

Since I didn't have anything better to do a Sunday evening, I tried to find the answer myself (lots and lots of Googling). I think my problem is that when I go back one period in time, I end up outside of my selection. What I did was to clear the two fields (Year, Month) that the user can select, and let the field PeriodCounterCurrent work. My new code looks like this,

= COUNT ( {$ <  Month=, Year=,  PeriodCounterCurrent = {$(#=MIN(PeriodCounterCurrent)-1) }   > } DISTINCT EmployeeNumber)

It works and I'm happy. Can someone tell me that I'm right?

1 Reply
Not applicable

Re: In set-analysis, how to I get the data for one YearMonth-period prior to first period in my selection?

Since I didn't have anything better to do a Sunday evening, I tried to find the answer myself (lots and lots of Googling). I think my problem is that when I go back one period in time, I end up outside of my selection. What I did was to clear the two fields (Year, Month) that the user can select, and let the field PeriodCounterCurrent work. My new code looks like this,

= COUNT ( {$ <  Month=, Year=,  PeriodCounterCurrent = {$(#=MIN(PeriodCounterCurrent)-1) }   > } DISTINCT EmployeeNumber)

It works and I'm happy. Can someone tell me that I'm right?

Community Browser