topic Re: Set analysis in QlikView

Set analysis

Wed, 07 May 2014 19:19:42 GMT

I am pretty new to set analysis. I have a field containing periods (YYYYMM). When I select a period I want one of my columns to show the sum of registered hours for all periods <= the selected period. How do I do this?

I also want to exclude all employees who are not still working for us after the period selected in the other field. For that I have a field containing the employees end of employment period. For those who are still working for us I thought I could enter a fictive value in the script that is far ahead in the future so that I could write in the expression that I want to show records where end of employment period is > the period selected in the other field. Is that possible?

Re: Set analysis

EduardoAlexandredeFrancisco — Wed, 07 May 2014 19:44:30 GMT

Maja...

Declare Variable

SET vDateBug = DATE(Max([Data Ocorrência Estoque]))

COUNT(

{ <[Date Bug] = {'<$(vDateBug)'}
>}DISTINCT [ID Bug])

Re: Set analysis

Wed, 07 May 2014 20:05:42 GMT

Hi Maja,

You can start with this:

count({< Period={"<=Date(Max(Period), 'YYYYMM')"} >} Hours)

Regards,

Ricardo Reaño

Re: Set analysis

Thu, 08 May 2014 05:05:24 GMT

I don't really understand what I need the count function for but I tried to apply what you wrote on my expression and wrote it like this:

sum({<EMPLOYEETYPE = {1}, REGDATE_PERIOD = {"<=max(REGDATE_PERIOD)"}>} HOURS)

But it results in zeros. I put max(REGDATE_PERIOD) in the label just to check that it shows correctly and it shows 201403 just like what was selected in the listbox. So I don't understand why it doesn't work.

/Maja

Re: Set analysis

tresB — Thu, 08 May 2014 05:10:10 GMT

Try:

sum({<EMPLOYEETYPE = {1}, REGDATE_PERIOD = {"<= $(=max(REGDATE_PERIOD))"}>} HOURS)

Edit:

And if you find this too is not working, it would be a date format issue. Then, try to share your sample.

Re: Set analysis

Thu, 08 May 2014 05:51:08 GMT

Thank you so much. That worked.