topic Re: Set Analysis Query in QlikView

Set Analysis Query

Mon, 02 Jun 2014 14:28:07 GMT

I have allowed user to select a start and an end date

v_Start=calender select floor(num(date))

v_End=Calender select Floor(num(date))

I want to display the meter reading on the start date and the end date.

However there might not be records on the start date or the end date

Site	Earliest Date From Selection	Reading(kWh) From 01/05/2014	Latest Read Date From Selection	Reading(kWh) To 01/06/2014	Total Production (kWh) for Selection
XXX1	08/05/2014	No Reading on this day	01/06/2014	5.98	5.98
XXX2	01/05/2014	19601.22	01/06/2014	23488.15	3886.93
XXX3	01/05/2014	21116.09	27/05/2014	No Reading on this day	-21116.09

it would be better to display the Meter reading on any of the days that falls within the selection, in the above case the selection is 1/5/2014 to 1/6/2014.

I have tried the following set analysis with no success. NDate being a numeric date:

Sum({$<Metric_Type={Meter_Read},NDate={$(=Min(NDate))}>}Data)

Sum({<Metric_Type={Meter_Read},NDate=[$(=Aggr(min({$}NDate),MSN))}>}Data)

Any help would be much appreciated.

Re: Set Analysis Query

Mon, 02 Jun 2014 14:34:53 GMT

Your question and problem's description is very unclear.

What is the problem, what doesnt work properly?
What exactly do you want to do?

Re: Set Analysis Query

jvitantonio — Mon, 02 Jun 2014 14:35:14 GMT

Hi,

Try this:

Sum({$<Metric_Type={Meter_Read},NDate={">=$(v_Start) <= $(v_End)"}>}Data)

Make sure that NDate is in the same format as v_Start and v_End.

Best,

Re: Set Analysis Query

Mon, 02 Jun 2014 14:38:06 GMT

sum({$<Metric_Type={'Meter_Read},NDate = {">=$(v_Start)<=$(v_End)"}>}Data)

Re: Set Analysis Query

Mon, 02 Jun 2014 14:45:57 GMT

My Mistake, sorry.

For Columns C and E I want equations that will display the meter readings that are available on the dates displayed in columns B and D respectivly. Column F is just column C- E. The dates in the headings are user selected, but does not mean that the data is available on the day the user has selected. hence I need to display what is availble within the date selection.

The equation I supplied as an example will be used in Column C, currently it just seeks for user selected date rather than possible selection.