topic Re: Set Analysis Sum Across Dimension in QlikView

Set Analysis Sum Across Dimension

erikzions — Tue, 13 Mar 2018 21:27:56 GMT

I have a table with a date dimension. I need to sum the previous quarters balance. I have a field for the previous quarter. I am having a hard time getting my set analysis to sum a previous date in the current dates dimension. I have tried many things. Below are two attempts. I must be having a brain cramp because I know I have done this before. Any help?

sum({< [As of Date] = {PreviousQuarter} >} [Balance])

also tried

sum({< [As of Date] = P({< [As of Date] = {'=PreviousQuarter'} >}) >} [Balance])

Date	Previous Quarter	Balance	Previous Quarter Balance
Sep 17	N/A	50	N/A
Oct 17	Sep 17	75	75
Nov 17	Sep 17	100	75
Dec 17	Sep 17	125	75
Jan 18	Dec 17	150	125
Feb 18	Dec 17	175	125

Re: Set Analysis Sum Across Dimension

swuehl — Tue, 13 Mar 2018 21:34:21 GMT

Do you have created an AsOf Table that really links to the previous quarter? How does your model look like?

Or maybe just use

=Above(Sum(Balance))

Re: Set Analysis Sum Across Dimension

erikzions — Tue, 13 Mar 2018 21:54:36 GMT

No link to the previous quarter. Just a field in the calendar table of previous quarter.

=Above would work except I have more than just quarters, and the user can select non sequential dates.

Re: Set Analysis Sum Across Dimension

swuehl — Tue, 13 Mar 2018 22:02:02 GMT

An AsOf table may solve your problem:

The As-Of Table

Re: Set Analysis Sum Across Dimension

erikzions — Mon, 26 Mar 2018 18:51:08 GMT

I have been unable to get this to work. I have included a simple example of my needs. My goal to get a quarter to date percent. Any help would be appreciated.

Re: Set Analysis Sum Across Dimension

sunny_talwar — Mon, 26 Mar 2018 19:00:30 GMT

One option is to do like this

Data:

LOAD * INLINE [

Date, Balance

09/30/2017, 50

10/31/2017, 75

11/30/2017, 100

12/31/2017, 125

01/31/2018, 150

02/28/2018, 175

];

Dates:

LOAD * INLINE [

Date, Previous Quarter

09/30/2017, 06/30/2017

10/31/2017, 09/30/2017

11/30/2017, 09/30/2017

12/31/2017, 09/30/2017

01/31/2018, 12/31/2017

02/28/2018, 12/31/2017

];

Left Join (Dates)

LOAD Date as [Previous Quarter],

Balance as [Previous Quarter Balance]

Resident Data;

Re: Set Analysis Sum Across Dimension

sunny_talwar — Mon, 26 Mar 2018 19:09:38 GMT

This might be the asoftable approach

Data:

LOAD * INLINE [

Date, Balance

09/30/2017, 50

10/31/2017, 75

11/30/2017, 100

12/31/2017, 125

01/31/2018, 150

02/28/2018, 175

];

AsOfTable:

LOAD Date as AsOfDate,

Date,

'CQ' as Flag

Resident Data;

Concatenate (AsOfTable)

LOAD Date as AsOfDate,

Date(QuarterStart(Date)-1) as Date,

'PQ' as Flag

Resident Data;

Re: Set Analysis Sum Across Dimension

erikzions — Thu, 29 Mar 2018 17:42:44 GMT

This approach will work. I was hoping for a front end solution so I would not have to change my data model. This solution requires extensive changes to my dashboards to accommodate a current quarter set analysis in each balance total. It also requires all users to know set analysis to correct. If there is no other way though, I will have to implement it like this. Thank you for your help. I will mark this correct in a little while to see if there is a more correct answer out there.

Re: Set Analysis Sum Across Dimension

Digvijay_Singh — Thu, 29 Mar 2018 18:09:45 GMT

Not sure but u may see if this expression can help in some way, PQB is expression label -

if([Previous Quarter]=Above(Total Date),Above(Total Balance),if([Previous Quarter]=Above(Total [Previous Quarter]),Above(Total PQB)))

Re: Set Analysis Sum Across Dimension

swuehl — Thu, 29 Mar 2018 18:13:59 GMT

The AsOf table is an additional table that links to your existing Date / Quarter dimension.

You only need the AsOf dimension and set analysis where you need to compare current / previous quarters in the same table.

For every other chart that is just using the current quarter data, just use the already existing dimension, no need to use the type field (or Flag as it is called in Sunny's example) in a set expression.

Re: Set Analysis Sum Across Dimension

Digvijay_Singh — Thu, 29 Mar 2018 18:14:12 GMT

PFA