topic GetFieldSelections in set analysis in QlikView

GetFieldSelections in set analysis

Wed, 07 Apr 2010 11:40:44 GMT

I have seen a few examples of loading scripts with a "current month" flag and "last year flag" etc, but I do not think it works for my example.

I have "Month" and "Year" set up as list boxes, so that a user can select the date period they want to look at.

I want to add a table that shows "sales current month" vs "sales 12 months ago", based on user selections.

I think it should look something like this, but can't get it to work:

Sum

({<Month={GetFieldSelections("Month")}, Year={GetFieldSelections("Year")}>} SALES)

Sum ({<Month={GetFieldSelections("Month")}, Year={GetFieldSelections("Year")-1}>} SALES)

I get sales for Feb 2010 if i type in the following code: Sum ({<Month={"Feb"}, Year={"2010"}>} SALES)

I get the Month value (Feb) returned if I type the following code: GetFieldSelections("Month")

but when I try and combine the 2 into one formula it does not work. Any ideas?

GetFieldSelections in set analysis

Miguel_Angel_Baeyens — Wed, 07 Apr 2010 11:50:12 GMT

Hello,

Syntax should look like

Sum({< Month = {"$(=GetFieldSelections('Month'))"}, Year = {"$(=GetFieldSelections('Year'))"} >} SALES)

That might work.

Nevertheless, I recommend you to read the following post, which approach is very close to you, in my opinion:

set analysis, a field equal to a selection field?

Regards

GetFieldSelections in set analysis

Wed, 07 Apr 2010 13:13:38 GMT

Thanks Miguel. With your code, plus a check through the link you sent me, I worked it out.

This is how to show sales in the same month last year:

Sum ({< Month = {"$(=GetFieldSelections("Month"))"}, Year = {"$(=GetFieldSelections("Year")-1)"} >} SALES)

GetFieldSelections in set analysis

Wed, 02 Jun 2010 16:34:31 GMT

What if mulitple years are selected? Is there a way to do that?

GetFieldSelections in set analysis

Thu, 03 Jun 2010 07:42:38 GMT

I am not quite sure what you mean? In my example, Sales from the currently selected Year & Month(s) are compared to the same period from the previous year (eg Jan'10 vs Jan'09).

If multiple years are selected, what are you trying to compare against?

Eg if the user selects Month=Jan & Year= 2009,2010, what do you want to show in your table?

a) Sales (Jan'09) vs Sales (Jan'10)

b) Sales (Jan'09+Jan'10) vs Sales (Jan'08+Jan'09)

c) Something else?

🐵

GetFieldSelections in set analysis

Miguel_Angel_Baeyens — Tue, 15 Jun 2010 08:13:42 GMT

Hello Kristen,

Actually there is. Say you want to analyze information from 2009 and 2010. Hardcoded set analysis would look like

Year = {2009, 2010}

GetFieldSelections will allow you to do that. The problem is when you need to check for alphanumeric values, say month "jan" and "feb" hardcoded woul look like

Month = {'jan', 'feb'}

The post I referred above is very illustrative on how to do this, regardless the number of values you want to match with.

Regards.

GetFieldSelections in set analysis

Wed, 16 Jun 2010 13:56:38 GMT

Hi Guys,

I have someproblem same like this. Can you guys help.

here is the link

http://community.qlik.com/forums/t/30418.aspx

Thanks

Sravan