Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ashleywooldridg
Contributor II
Contributor II

Set analysis to exclude all table fields except 1

I am trying to show a column in a straight table that will only ever show the sales from last trade month.

I can show this using the flag i created, but when a user makes a selection in the SaleDate calendar for a date like last year the value goes to 0, where i want it to stay static.

Expression used currently: =Sum({$<[SaleDate.Trade Month Flag]={'1'}>} [Sale QTY Sold])

I believe I could add like below, but would like to know if there is a way where i do not have to enter every column in the Calendar table to exclude them all?

The Long way: =Sum({$<[SaleDate.Trade Month Flag]={'1'}, SaleDate.Year= , SaleDate.Month=, etc...>} [Sale QTY Sold])


Thank you

1 Solution

Accepted Solutions
sunny_talwar

May be look here

Ignore all selections except some specific fields using Set Analysis

Use $Table and $Field to create a concatenated list of all fields from your SaleDate table

View solution in original post

5 Replies
vishsaggi
Champion III
Champion III

Try this:

=Sum( TOTAL {$<[SaleDate.Trade Month Flag]={'1'}>} [Sale QTY Sold])

ashleywooldridg
Contributor II
Contributor II
Author

Sorry, this did not have the desired effect.

If i select any other date from the SaleDate calendar that does not encompass the entire Trade Month the value changes still.

sunny_talwar

May be look here

Ignore all selections except some specific fields using Set Analysis

Use $Table and $Field to create a concatenated list of all fields from your SaleDate table

ashleywooldridg
Contributor II
Contributor II
Author

Thank you Sunny, I had to re-read a couple of times, but managed to get what i needed with the below.

=Sum({$<[$(=Concat({1<$Table={[SaleDate]}>}distinct $Field,']=, [')&']='), [SaleDate.Trade Month Flag]={'1'}>} [Sale QTY Sold])

braham
Creator
Creator

Afternoon

I have been looking for a method to exclude selection of fields from a specific table when performing a calculation. I came across this thread and tried to use the formulae listed, but replacing the file and table names with ones from my QV model. I understand what the formulae is trying to do, but cannot get it to work in my environment. I get a red underline from the first '[' and my first and last ')' is highlighted in red. I am sure that I have copied the formulae correctly. Is there a crucial symbol missing from the formulae?

Any suggestions will help. I am running QV version 12 (April 2020 SR2)