Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

22 Replies
Not applicable
Author

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

   

Reporting DateSales Transaction IDSales Value
31-Dec1100
31-Dec2200
31-Jan3300
31-Jan4400
28-Feb5500
28-Feb6600
MK9885
Master II
Master II

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

Some thing like this:

sunny_talwar

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

sunny_talwar

Are you using alternate state?

MK9885
Master II
Master II

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

SalesSum Sales - Reporting Date 1Sum 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

Anonymous
Not applicable
Author

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])

Not applicable
Author

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.