Set analysis to exclude all table fields except 1

[ashleywooldridg]

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

[vishsaggi]

Try this:

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

[ashleywooldridg]

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 · Accepted solution]

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]

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]

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)