topic Help with set analysis in QlikView

Help with set analysis

onetwothree123 — Mon, 11 Jun 2012 13:32:45 GMT

I have two variables

Currentday = 06/11/2012

Previousmonth = 05/12/2012.

I am trying to write a set expression in my line chart using these two variable where date lies between Currentday to Previous month

Date is the dimension

Re: Help with set analysis

Miguel_Angel_Baeyens — Mon, 11 Jun 2012 13:42:02 GMT

Hi AJ,

Assuming the format returned by the variables and the field is the same, let's say DD/MM/YYYY, something like the following should do.

Sum({< Date = {'>=$(=Date(Currentday))<=$(=Date(Previousmonth))'} >} Sales)

Note the single quoting, the equals and that QlikView is case sensitive for variable names as well as field names.

Hope that helps.

Miguel

Help with set analysis

onetwothree123 — Mon, 11 Jun 2012 13:59:56 GMT

Miguel,

Avg({< Date = {'>=$(=Date(VarCurrentDay))<=$(=Date(VarPreviousMonth))'} >} Deposit)

Here what I am getting with above expression, do I have to adjust some settings

I have data(Dates) from May2010 to Aug2012

Re: Help with set analysis

Miguel_Angel_Baeyens — Mon, 11 Jun 2012 14:05:22 GMT

Hi,

It seems that the set analysis is not being evaluated correctly for some reason. Did you check that formats in both Date field and Date(VarCurrentDay) function return the same? Is the "As of" calendar object using a variable or a value from a field?

Miguel

Help with set analysis

onetwothree123 — Mon, 11 Jun 2012 14:11:08 GMT

Date and Date(VarCurrentDay) are in same format MM/DD/YYYY and

As of calendar object is using a variable VarCurrentDay with Min Value = Min(Date) and Max value= Max(Date)

Re: Help with set analysis

Miguel_Angel_Baeyens — Mon, 11 Jun 2012 14:20:17 GMT

Hi,

Then that should work. Check the application attached that addresses a very similar issue just in case.

Hope that helps.

Miguel

Help with set analysis

onetwothree123 — Mon, 11 Jun 2012 14:35:41 GMT

Miguel,

I want line chart to dynamically display between Current slected date from calendar object to 30 days prior to selected date

if user select 06/11/2012 then line chart should display from

05/10/2012 to 06/11/2012. But looks like example application you just sent is showing all dates same is happening with my application as I have date from May 2010 to Aug 2012.

Re: Help with set analysis

Mon, 11 Jun 2012 14:39:18 GMT

may be This help Full for you

Re: Help with set analysis

Miguel_Angel_Baeyens — Mon, 11 Jun 2012 15:28:20 GMT

Hi,

I've just checked the application. It's working fine here, select for example 22/08/2012. Reload it again.

Miguel

Help with set analysis

onetwothree123 — Mon, 11 Jun 2012 16:41:00 GMT

Miguel,

Quick question why line chart is including 05/10/2012 31/10/2012 ?

I just want line chart to to be displayed from 22/07/2012 to 22/08/2012

This is what I am having hard time understanding

Re: Help with set analysis

Miguel_Angel_Baeyens — Mon, 11 Jun 2012 16:42:34 GMT

Hi AJ,

Is that the latest version I uploaded? vCurrentDate will always be greater than vPreviousDate and I corrected that mistake in the second version.

Regards.

Miguel

Help with set analysis

onetwothree123 — Mon, 11 Jun 2012 17:57:23 GMT

Miguel,

I just see one version from you.

qcomVariablesDate.qvw

Its an images from that version only.

vCurrentDate will always be greater than vPreviousDate ?

I didn't get that

Re: Help with set analysis

Miguel_Angel_Baeyens — Mon, 11 Jun 2012 18:22:15 GMT

Hi AJ,

Indeed, vCurrentDate will always be greater than vPreviousDate, the latter is defined to be one month before. You can see that in the Settings menu, Variable Overview.

The logical expression must be (from the lower date -one month ago- to the higher -today):

>=vPreviousDate<=vCurrentDate

Hope that makes sense.

Miguel

Help with set analysis

onetwothree123 — Mon, 11 Jun 2012 19:22:27 GMT

Miguel,

I got it.

But ran into another problem may be it is because of data I have in Excel.

Its not doing the Avg. of Deposit based on dates

Basically its giving me the same value in line chart as it is in Excel.

In excel I have Date field with dates from May2010 to Aug2012.