Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to exclude a dimension in set analysis

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 DateTotal RRP
2016/04/03$91,907,319
2016/03/27$93,333,504
2016/03/20$94,149,907

Reporting DateCategory ACategory BCategory 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

1 Solution

Accepted Solutions
sunny_talwar

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?

View solution in original post

2 Replies
engishfaque
Specialist III
Specialist III

Dear Susan,

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


Kind regards,

Ishfaque Ahmed

sunny_talwar

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?