How to exclude a dimension in set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Can anyone please let me know what's wrong with the below expression?

I'm trying to create 2 functions in the text box as below:

Reporting Date	Total RRP
2016/04/03	$91,907,319
2016/03/27	$93,333,504
2016/03/20	$94,149,907

Reporting Date	Category A	Category B	Category C
2016/04/03	$15,234,570	$50,000,000	$26,672,749
2016/03/27	$14,754,582	$50,000,000	$28,578,922
2016/03/20	$14,345,164	$50,000,000	$29,804,743

Text Box 1: I want text box 1 to return the value of Category A when a reporting date is selected, however when the reporting date is not specified, I want it to return the value of Category A of the latest reporting date.

=NUM(SUM({$<[Reporting Date]={"$(=max([Reporting Date]))"}>}{<Category={'Surplus'}>} [Unit RRP]*Quantity),'$ #,##0', '.',',')

Text Box 2: I want text box 2 to return the value of Category A of the prior reporting week, however when the reporting date is not specified, it should still return the value of Category A of the prior week of the latest report.

=NUM(SUM({$<[Reporting Date]={"$(=date(WeekStart(max([Reporting Date]),-1,-1),'YYYY/MM/DD'))"}>} {$<Category={'Surplus'}>} [Total RRP]),'$ #,##0', '.',',')

Basically when Reporting Date - "2016/03/27" is selected, I want Text Box 1 to return $14,754,582 and Text Box 2 to return $14,345,164

When reporting date is NOT selected, Text Box 1 should return $15,234,570 and Text Box 2 should return $14,754,582.

Currently, for some reason box of my text boxes are returning the same value, when the reporting date is not specified, both text box 1 & 2 return the total value of Category A - $54,334,316 of ALL reports, when for example reporting date "2016/03/27" is selected, both text box 1&2 are returning $14,754,582.

Thank you

Susan

Re: How to exclude a dimension in set analysis

engishfaque — Fri, 08 Apr 2016 16:56:39 GMT

Dear Susan,

=NUM(SUM({$<[Reporting Date]={"$(=max([Reporting Date]))"}, Category={'Surplus'}>} [Unit RRP]*Quantity),'$ #,##0')

Kind regards,

Ishfaque Ahmed

Re: How to exclude a dimension in set analysis

sunny_talwar — Fri, 08 Apr 2016 17:02:47 GMT

In addition you might need to add Date() to your set analysis:

=Num(Sum({$<[Reporting Date]={"$(=Date(Max([Reporting Date]), 'YYYY/MM/DD'))"}, Category={'Surplus'}>} [Unit RRP]*Quantity),'$ #,##0')

If this still doesn't work, would you be able to provide a sample where you have Category?

topic Re: How to exclude a dimension in set analysis in QlikView

How to exclude a dimension in set analysis

Re: How to exclude a dimension in set analysis

Re: How to exclude a dimension in set analysis