topic Re: Help with Set Analysis in QlikView

Help with Set Analysis

Anonymous — Fri, 12 Oct 2012 18:20:45 GMT

I am struggling with the set analysis syntax for my calculation. I am try to get a backlog number of records that where submitted before a fixed date and didnt have have a closed date or the closed date is greater than the fixed date.

My problem is I need to get a backlog number to start with. If you my chart dates start at now less 365. I want each column in the chart to show the backlog before that date.

I am not very good with the Set Analysis syntax.

How do I make this caluculation work with Set Analsyis.

Submit Date is September 2011. Closed Date is December 2011. For October I want to show that record as one backlog from the previouse month.

Count( where Submit_Date <= (Date in Chart) and Closded_ Date > than (Date in Chart) or Closed_Date=Null

Is there a way to capture field data into a variable. Like capture October 2011 to compare.

Any help would be greatly appreciated.

Thank you,

cassadi

Re: Help with Set Analysis

Sat, 13 Oct 2012 11:24:40 GMT

hi Check below syntex

For Date Range

1. Count( { $ < Submit_Date = { "<=$([Date in Chart]) }, Submit_Date = { "<=$([Date in Chart]) } > } [Field On Chart] )

For Null

2. Count( { $ < Closed_Date = {""} > } [Field On Chart])

Hope these will help

- Anil

Re: Help with Set Analysis

chematos — Mon, 15 Oct 2012 17:46:23 GMT

I´m assuming that Submit_Date <= (Date in Chart) must be in both cases where Closded_ Date > (Date in Chart)

and Closed_Date=Null

Try with this syntax:

Count( { < Submit_Date = { '<=[Date in Chart]'}, Submit_Date = { '<=[Date in Chart] '} >

< Submit_Date = { '<=[Date in Chart]'}, Submit_Date = { '=$(=null())' } >} [Field On Chart] )

Regards,

- Chema

Re: Help with Set Analysis

Anonymous — Mon, 15 Oct 2012 17:51:05 GMT

I have a built the dates into a context table so I can look at closed and submitted records in the same month. The dates are joined to a master calendar. The Date field is called SelectedMonthYear.

This expression works when I have the Demenision = Deparment

(((count({<DEFECT_TYPE={'Defect'},CONTEXT={"DR_Opened"}>}Distinct BUG_ID))-(count({<DEFECT_TYPE={'Defect'},CONTEXT={"DR_Closed"}>}Distinct BUG_ID)))+(count({<Defect_Open={"Not_Closed"}>} distinct BUG_ID)))

But when I try to used the Date field of SelectedYearMonth it doesnt calculate right.

I am trying to be able to look at backlog in the past even if the currently the record has a closed date.

Thank you for all your help.

Re: Help with Set Analysis

Anonymous — Mon, 15 Oct 2012 17:52:19 GMT

I have a built the dates into a context table so I can look at closed and submitted records in the same month. The dates are joined to a master calendar. The Date field is called SelectedMonthYear.

This expression works when I have the Demenision = Deparment

But when I try to used the Date field of SelectedYearMonth it doesnt calculate right.

I am trying to be able to look at backlog in the past even if the currently the record has a closed date.

Thank you for all your help.

Re: Help with Set Analysis

Anonymous — Mon, 15 Oct 2012 17:53:16 GMT

I have a built the dates into a context table so I can look at closed and submitted records in the same month. The dates are joined to a master calendar. The Date field is called SelectedMonthYear.

This expression works when I have the Demenision = Deparment

But when I try to used the Date field of SelectedYearMonth it doesnt calculate right.

I am trying to be able to look at backlog in the past even if the currently the record has a closed date.

Thank you for all your help.

Re: Help with Set Analysis

chematos — Tue, 16 Oct 2012 14:00:26 GMT

What happened about the first question?

I don´t know how yor are relating dates, may be you have different types for these dates and the calendar dates.

You need to have the same type and format. Make sure of that.