22 Replies Latest reply: Jan 19, 2017 10:41 AM by Wallo Atkinson

# Set Analysis Expressions - Inputs from Filter Applied

I have the below set analysis expression which calculates the Sum of Sales for the Reporting Date 31-Dec-2013.

sum({\$<[Reporting Date] = {'31/12/2013'}>} [Sales])

In this I have hardcoded the value 31-Dec-2013. Instead, I need to define this expression, where the Date comes as input from a Filter selection that the user makes.

If user selects 31-Jan-2014, it should be computed for this date. Is there a way to make the expressions generic and receive inputs based on user selections?

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

Correct me if I'm wrong...

If a user selects 2014, he should view 2013?

You want previous year as a filter?

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

If your user selects a single date specifically then this basic Sum is all that is needed:

Sum( [Sales] )

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

That is correct. I gave a single example, for ease of understanding. I have explained in detail in one of the posts below. I am doing trend comparisons between two dates for Sales and both dates have to come from a filter selection.

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

May be this:

Sum({\$<[Reporting Date] = {"\$(=Date(AddMonths(Max([Reporting Date]), -1), 'DD/MM/YYYY'))"}>} [Sales])

This will show you 1 month prior sales based on your selection.

Also, see here:

QlikView Date fields

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

If your scenario is more complicated than what Petter described, you can use dollar sign expansions to create dynamic values for the set modifier:

Dates in Set Analysis

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

I looked at the links. Looks like the first expression can be dynamically defined to take current filter selection. The second set analysis expression has to use predefined functions like Minus 1 month or Minus 1 year. My requirement is two fields with two separate expressions and each expression has a separate filter linked to it.

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

All,

The report will look some thing like this.

 Sales Sum Sales - Reporting Date 1 Sum Sales - Reporting Date 2

There will be two filters.

Filter 1 : Selection for Reporting Date 1

Filter 2: Selection for Reporting Date 2

If user selects Reporting Date 1 as 31-Dec, column two should display Sum of sales for 31-Dec.

If user selects Reporting Date 2 as 31-Jan, column three should display Sum of sales for 31-Jan.

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

Reporting Date 1 and Reporting Date 2 are variables or do you have fields? If they are fields, are they Island Table fields?

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

They are fields.

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

Are they fields from Island table? Can you share how your data model looks?

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

I do not understand the term Isalnd Table. You can assume, the data looks in this format.

 Reporting Date Sales Transaction ID Sales Value 31-Dec 1 100 31-Dec 2 200 31-Jan 3 300 31-Jan 4 400 28-Feb 5 500 28-Feb 6 600
• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

What is the condition to differentiate between Reporting Date1 and Reporting Date2?

As it is a single field 'Reporting Date'

Before or after any particular??

Ex:

before 31 Dec is Reporting Date1

After 31 Dec is Reporting Date2

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

That input comes dynamically from the filter selections.

Filter 1 and Filter 2 will be separate filters for user to choose the Reporting Dates.

Filter 1 selection should be Reporting Date 1

Filter 2 selection should be Reporting Date 2

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

What exactly is a filter my friend? Is it a field or input field associated with a variable?

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

Ok but how are you calculating the 2nd and 3rd column?

 Sales Sum Sales - Reporting Date 1 Sum Sales - Reporting Date 2

You should understand that to create separate columns you'll need different expressions.

Alternate State will not be valid on a single chart if we have separate filters.

Best case is create 2 filters from same field, name it as ReportingDate1 and Reporting Date2

Set alternate states for those as 1 & 2

Then create 2 charts with alternate states as 1 & 2 and overlay them upon each other with conditional expression but again you'll need 2 different fields or values to separate those 2 charts.

I'm sure experts here may have better understanding but I'll take this approach

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

I will put the requirement this way. Column 2 and 3, the reporting dates have to be dynamically determined based on a user input. The end user should dermine what should be Reporting Date 1 and Reporting Date 2. It could be through any user interface options (My limited end user QVknowledge makes me think of only Filters).

When the application is published, the end user should not be viewing codes/changing the values assigned to variables. Instead, the user should see two options i.e. to choose two dates for which the corresponding numbers displayed in the table chart.

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

It sounds like you would want to use 2 Slider/Calendar object to populate variables.  One for a start date and one for the end date

(You can get the the object to be a date pick list.)

Then you would just use the variables tied to the objects in your set analysis.

Similar to this Selecting Arbitrary Date Ranges.

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

I have taken a look at the video. Yes. Some thing like this. The video shown is in Qv I believe. Can we do the same in QS? 2 Slider/Calendar is an external object or can this be built within QS?

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

Sorry, I don't know enough about Qlik Sense to give you an answer on that.  Maybe someone else here can.

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

Some thing like this:

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

Are you using alternate state?

• ###### Re: Set Analysis Expressions - Inputs from Filter Applied

You probably want to use variables.

Could vary a based on exactly what you're trying to get to but something like:

vFirstDate=getselectedvalue(Period)  and vSecondDate=\$(vFirstDate)-1

Then in your set analysis you would reference the variables.  Something like:

sum({\$<[Reporting Date] = {\$(vFirstDate)}>} [Sales])