Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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?

View solution in original post