Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
erikzions
Creator
Creator

Set Analysis Sum Across Dimension

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])

DatePrevious QuarterBalancePrevious Quarter Balance
Sep 17N/A50N/A

Oct 17

Sep 177575
Nov 17Sep 1710075
Dec 17Sep 1712575
Jan 18Dec 17150125
Feb 18Dec 17175125
1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

10 Replies
swuehl
MVP
MVP

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))

erikzions
Creator
Creator
Author

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.

swuehl
MVP
MVP

An AsOf table may solve your problem:

The As-Of Table

erikzions
Creator
Creator
Author

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. 

sunny_talwar

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;

sunny_talwar

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;

Capture.PNG

erikzions
Creator
Creator
Author

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.

Digvijay_Singh

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)))

Capture.PNG

swuehl
MVP
MVP

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.