topic Re: Set Analysis with a Variable in QlikView

Set Analysis with a Variable

Thu, 09 Oct 2014 19:56:35 GMT

I am trying to build a line chart that always displays data for the current fiscal year only. I'm using Set Analysis because I don't want the user to have to select the months/year from a list box.

The fiscal year starts on 10/1 every year (10/1/14 to 9/30/15 is this fiscal year). I have created a variable that calculates the first day of the fiscal year (vFiscalYearStartDay). I tested my variable with a text box and it seems to be calculating properly:

However, when I put it in my expression, it does not work.

Here is what I tried:

=Sum({$< [Transaction Date] ={">=$(=vFiscalYearStartDay)"}>} Amount)

I get a 'No Data to Display' error

I manually put the date into the expression like this:

=Sum({$< [Transaction Date] ={">=10/1/2014"}>} Amount)

and it works fine.

Can anyone tell me what I am doing wrong ? I've never tried Set Analysis with a variable so this is all new to me.

Re: Set Analysis with a Variable

JonnyPoole — Thu, 09 Oct 2014 20:07:03 GMT

What is the exact variable definition? does it have an '=' in the front or no.

For example this works for me:

vFiscalDate variable formula: =yearstart(max(Date))

successful chart expression: =Sum( {$<Date = { ">=$(vFiscalDate)"}>} Sales)

Re: Set Analysis with a Variable

Thu, 09 Oct 2014 20:40:03 GMT

This:

If($(Qvc.IsLeapYear)=1,

If(Num(month(today()))>=10,Date(YearStart(Today()+364)-92),Date(YearStart(Today())-92,)),

If(Num(month(today()))>=10,Date(YearStart(Today()+365)-92),Date(YearStart(Today())-92,))

)

No equal sign.

Re: Set Analysis with a Variable

jaimeaguilar — Thu, 09 Oct 2014 21:09:20 GMT

Hi,

In order to make your formula work, you have to precalculate what it's inside your variable. Actually that's what the "=" at the beginning of the variable definition means.

For example a variable defined as Date(today()) and another one =date(today()) would behave differently depending on the context where you use each variable. The first will calculate date(today()) inside the chart everytime a user makes a selection. The second option will precalculate date(today()) and the chart where you use this variable will receive just the result.

In you case you need that the variable you use, returns you a number or any result, so set analysis can use this value. That's why you need to use a "=" (precalculate) at the beginning of your variable definition,

regards

Re: Set Analysis with a Variable

JonnyPoole — Fri, 10 Oct 2014 02:18:55 GMT

OK... jamie is right in that its easier to just add an '=' on the front of your variable. The SET ANALYSIS syntax will be simpler and it may actually start working by just adding an =. To be sure you'd have to share the QVW.

BUT.. its not 100% necessary.

The following syntax is also valid for a variable that does not have a preceding =

Sum( {$<Date={$(='">=' & $(vStartofYear) & '"') }>} Sales)

This evaluates to the following (see screenshot with red circle)

In this situation you are and building the entire SET MODIFIER formula from scratch which IS possible but a bit of a pain.

Just had to nerd out there and get this out of my system