Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
DaithiOK
Contributor II
Contributor II

How to limit line chart totals outside of set analysis?

I am trying to recreate numbers in a table showing totals per month, in a line chart but my line chart takes in too many records and I don't know how to limit it.

My current logic is this:

I have a KPI that counts the number of objects that meet the criteria in the set analysis. Lets say its projects that have spent more than they should. The user will click on the KPI and a table will pop up showing all of the relevant projects, how much they spent, how much they are approved to spend and so on.

The way I limit the records that get pulled into the table is having a separate column called #Projects that replicates the KPI count so that each row in the table will have a 1 in that column if it matches the criteria. In the other expressions I then start by saying:

 

If(#Projects = 1,
  Set Analysis from KPI,
,0)

 

This prevents too many rows from populating the table as for some reason the suppress null values doesnt work properly in this regard.

 

My issue with the line chart is that i have no way of limiting the number of records that the expression pulls in in the same way i do in the table with if(#Projects=1) and so it basically pulls in everything so my totals are orders of magnitudes greater than they should be. 

 

My question is how can i limit the projects i pull in to the line chart such that only those that are in the table are represented?

I hope this makes sense because I am really stumped. 

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this

 

Sum(Aggr(

If(Sum(TOTAL <ProductCategoryName, ProductLine, ProductName, ProductSubcategoryName> SalesForecast) - Sum(TOTAL <ProductCategoryName, ProductLine, ProductName, ProductSubcategoryName> SalesAmount) > 1500, Sum(SalesAmount))

, ProductCategoryName, ProductLine, ProductName, ProductSubcategoryName, YearMonth))

 

View solution in original post

17 Replies
agigliotti
Partner - Champion
Partner - Champion

did you try using "Dimension limits" somehow?

agigliotti_0-1589280178645.png

I hope it can helps.

DaithiOK
Contributor II
Contributor II
Author

Hi,

Dimension limits will only limit my dimension (which is time) rather than my expression (which is number of projects). 

Thanks for the suggestion though

sunny_talwar

Would you be able to share a sample to show your issue?

erikzions
Creator
Creator

You should not do your record limiting in the dimension.  You should do it in the metrics.  That is where you will limit the rows that are in your table.  the dimension will be limited by default when the metrics are.  Add the set analysis you are doing to the calculation in your metric and this should clear it up.

DaithiOK
Contributor II
Contributor II
Author

Hey, Thanks for responding to me.

As per my reply to the other user...I am not trying to limit my dimension. I am trying to limit my expression. My questions asks how can I do this outside of set analysis because set analysis is not enough. 

DaithiOK
Contributor II
Contributor II
Author

Hey,

 

Yes I was thinking that I should do that as i don't think im explaining well enough. It will take a while for me to get an example though that mimics what i am doing. I will respond here when i do. Thanks. 

sunny_talwar

You might be able to use Sum(Aggr()), but it is difficult to give an exact expression.

DaithiOK
Contributor II
Contributor II
Author

I included a sample that I hope highlights the issue. In one table on the left I have left it basic with all records included. In the table on the right i have applied the same logic i described above as to how I have limited to rows to meet certain conditions (positive variance > €1500 in this case but in my real case it is much more complicated). 

I am essentially trying to replicate the left line chart but with the elements in the table on the right ie. for a much smaller sample set. However in a line chart it just pulls in all records. 

 

Any help would be appreciated or if i can clarify further if there are questions. 

sunny_talwar

Try this

 

Sum(Aggr(

If(Sum(TOTAL <ProductCategoryName, ProductLine, ProductName, ProductSubcategoryName> SalesForecast) - Sum(TOTAL <ProductCategoryName, ProductLine, ProductName, ProductSubcategoryName> SalesAmount) > 1500, Sum(SalesAmount))

, ProductCategoryName, ProductLine, ProductName, ProductSubcategoryName, YearMonth))