topic Set analysis to exclude all table fields except 1 in QlikView

Set analysis to exclude all table fields except 1

ashleywooldridg — Mon, 21 May 2018 02:31:02 GMT

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

Re: Set analysis to exclude all table fields except 1

vishsaggi — Mon, 21 May 2018 16:20:34 GMT

Try this:

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

Re: Set analysis to exclude all table fields except 1

ashleywooldridg — Mon, 21 May 2018 23:00:06 GMT

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.

Re: Set analysis to exclude all table fields except 1

sunny_talwar — Tue, 22 May 2018 01:14:33 GMT

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

Re: Set analysis to exclude all table fields except 1

ashleywooldridg — Tue, 22 May 2018 04:26:24 GMT

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

Re: Set analysis to exclude all table fields except 1

braham — Sun, 02 Oct 2022 13:26:46 GMT

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)