topic Re: Set Analysis Expression Aggregation Issue in QlikView

Set Analysis Expression Aggregation Issue

Mon, 03 Feb 2014 18:29:23 GMT

Hey All,

First of all, thank for the taking the time to view and assist. I may be overlooking something here but this is giving me a headache.

Objective: I'm looking to to reflect the various departmental collections within the selection (Month(s)) time frame. e.g. Bankrutpcy/Recovery by Time.

Problem: It's aggregating the departments collections throughout the entire data set (over all the Months), regardless of the {$} indication. Despite the user selecting a specific month, it reflects the the SUM throughout all of time.

Here is an example formulas

SUM({$<[Department] = {'Recovery'}>}[PRINPMT_28_1]) + SUM({$<[Department] = {'Recovery'}>} [PRINNSF_28_2])

To calculate the total collected, We add the PRINPMT_28_1 + PRINNSF_28_2 to reconcile the payments that bounced, or transactions that were removed from books.

The same formual exists in a different expression that shows how much of a particular amount was collected for a various client by the Bankruptcy department.

This makes a very easy and convenient way for the user to see how much of the "SUM" came from which department.

I hope I articulated this enough for everyone.

Thanks,

Re: Set Analysis Expression Aggregation Issue

Gysbert_Wassenaar — Mon, 03 Feb 2014 18:42:53 GMT

Sorry, but it's not articulated enough for me. It's impossible to tell from your post what's happening in your document. Try posting a sample document that demonstrates the problem. Read this document to find out how you can create a small non-confidential document: Preparing examples for Upload - Reduction and Data Scrambling.

Re: Set Analysis Expression Aggregation Issue

Anonymous — Mon, 03 Feb 2014 19:50:46 GMT

It is hard to tell, but let me guess...

If you want the expression result to be independent from selections ("regardless of the {$} indication"), you have to use set identifier 1 rater than $. Maybe this:

SUM({1<[Department] = {'Recovery'}>}[PRINPMT_28_1]) + SUM({1<[Department] = {'Recovery'}>} [PRINNSF_28_2])

But, as Gysbert Wassenaar said, can't tell for sure without an example

Re: Set Analysis Expression Aggregation Issue

Mon, 03 Feb 2014 20:46:21 GMT

It seems to be an issue related to the alternate states within the document. When I apply the alternate state, it disregards the current selection (which is NOT desired) opposed to me specifying the inhereted state it DOES consider the current selections.

Re: Set Analysis Expression Aggregation Issue

Mon, 03 Feb 2014 20:51:33 GMT

Noob Mistake

I didn't reference the alt. state appropriately.

({[Gross Dollar Comparison]<[Department] = {'Recovery'}>}[PRINPMT_28_1]) + SUM({[Gross Dollar Comparison]<[Department] = {'Recovery'}>}[PRINNSF_28_2])

Thanks for your time, I do appreciate it. Apologies for the lack of clarity.