set analysis ignore selection in calculation AND only show selected items

veldetta — Fri, 16 Sep 2022 01:10:35 GMT

I have the following set analysis

sum({1<DATE={">=1/1/2022"}>}AMOUNT)

I have a selection field called 'MONTH_VARIANCE'.

I want the set analysis to show all records despite a selection in 'MONTH_VARIANCE', however, I only want to filter for the dimension that matches has the 'MONTH_VARIANCE' selections in it.

How would i rewrite my set analysis statement?

Re: set analysis ignore selection in calculation AND only show selected items

sidhiq91 — Fri, 16 Sep 2022 03:47:09 GMT

@veldetta I am not sure if I understand your requirement correctly.

But if you wanted to ignore the MONTH_VARIANCE selection and not other dimensions then you can use the below expression.

sum({<DATE={">=1/1/2022"}, MONTH_VARIANCE = >}AMOUNT)

Please let me know if this worked. Or if you are looking for something else.

Re: set analysis ignore selection in calculation AND only show selected items

veldetta — Fri, 16 Sep 2022 04:35:29 GMT

@sidhiq91

I have the following table from script:

ID	Product	Date	Month	Amount	Month Variance	Amount Variance	Variance Range
123456	Product 2	5/31/2022	May	-14369	May - Apr	-14369	($80M)-0
123456	Product 1	5/31/2022	May	1500000	May - Apr	1500000	> $350M
123456	Product 3	5/31/2022	May	5943445	May - Apr	5943445	> $350M
123456	Product 2	6/30/2022	Jun	0	Jun - May	14369	$1 - $80M
123456	Product 1	6/30/2022	Jun	505680.37	Jun - May	-994319.63	< ($350M)
123456	Product 3	6/30/2022	Jun	6718530	Jun - May	775085	> $350M
123456	Product 2	7/31/2022	Jul	-13423	Jul - Jun	-13423	($80M)-0
123456	Product 1	7/31/2022	Jul	605739.81	Jul - Jun	100059.44	$80M - $350M
123456	Product 3	7/31/2022	Jul	7630697	Jul - Jun	912167	> $350M

which is put into a straight table that looks like this:

ID	Product	May	[May]-[Apr]	June	[June]-[May]	July	[July]-[June]	YTD	Variance YTD
		$7,429,076	$7,429,076	$7,224,210	-$204,866	$8,223,014	$998,803	$8,223,014	$8,223,014
123456	Product 3	$5,943,445	$5,943,445	$6,718,530	$775,085	$7,630,697	$912,167	$7,630,697	$7,630,697
123456	Product 1	$1,500,000	$1,500,000	$505,680	-$994,320	$605,740	$100,059	$605,740	$605,740
123456	Product 2	-$14,369	-$14,369	$0	$14,369	-$13,423	-$13,423	-$13,423	-$13,423

When I select [Month Variance] = 'Jul - Jun' and /or [Variance Range] = '> $350M' the table should show like this...

ID	Product	May	[May]-[Apr]	June	[June]-[May]	July	[July]-[June]	YTD	Variance YTD
		$5,943,445	$5,943,445	$6,718,530	$775,085	$7,630,697	$912,167	$7,630,697	$7,630,697
123456	Product 3	$5,943,445	$5,943,445	$6,718,530	$775,085	$7,630,697	$912,167	$7,630,697	$7,630,697

instead, I'm getting this:

ID	Product	May	[May]-[Apr]	June	[June]-[May]	July	[July]-[June]	YTD	Variance YTD
		$0	$0	$0	$0	$7,630,697	$912,167	$7,630,697	$912,167
123456	Product 3	$0	$0	$0	$0	$7,630,697	$912,167	$7,630,697	$912,167

or this

ID	Product	May	[May]-[Apr]	June	[June]-[May]	July	[July]-[June]	YTD	Variance YTD
		$0	$0	$0	$0	$7,630,697	$912,167	$7,630,697	$8,223,014
123456	Managed Investments	$0	$0	$0	$0	$7,630,697	$912,167	$7,630,697	$7,630,697
123456	Banking	$0	$0	$0	$0	$0	$0	$0	$605,740
123456	Custody	$0	$0	$0	$0	$0	$0	$0	-$13,423

(when i used sum({1<DATE={">=1/1/2022"}>}AMOUNT))

Notice that May / June and the respective variances are showing 0s and the Variance YTD is just matching the Month Variance selected. With those selections, I only want to see 1 line item with the values for the other months / variances unchanged. Been struggling with this for hours.

sum({1<DATE={">=1/1/2022"}>}AMOUNT) shows everything but I wanted to return those IDs that has the selected Month Variance

topic Re: set analysis ignore selection in calculation AND only show selected items in QlikView

set analysis ignore selection in calculation AND only show selected items

Re: set analysis ignore selection in calculation AND only show selected items

Re: set analysis ignore selection in calculation AND only show selected items