Line chart data points in percentage of X axis (column) vs percentage of total

[QlikChick]

Hi there, 

I have a line chart that looks like this:

The percentages are being calculated on the entire total count, and not on the week as I'm hoping for. The weeks are determined based on a master calendar I put in the load script. I know I need to add this in to my expression somewhere, but I can't figure out where. Here's my current expression:

Count({<Health>} distinct [Clients])
/
Count({<Health>} distinct total [Clients])

Any help on this is greatly appreciated, I am definitely at a stopping point until I can get it figured out.

Thanks!

[Oliver_F · Accepted solution]

HI,

you can specify the total command by adding a subset of dimenions in <> - See this article: https://help.qlik.com/en-US/sense/April2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/define-a...

Your formula should look similar to this I guess:

Count({<Health>} distinct [Clients])
/
Count({<Health>} distinct total <weekdimension> [Clients])

[QlikChick]

Hi there! 🙂

I tried this before and again just now, and it is not working. I feel like I need to use the aggr function, but I know that's involved and I'm not confident in understanding the correct syntax to use. 

Any further help is greatly appreciated! 

Thanks

J

[Oliver_F]

Hi,

actually it works quite well:

test:
load * inline [
Dimension,ClientCount, Week
A,1,1
B,2,1
C,3,1
A,100,2
B,200,2
C,300,2
A,1000,3
B,2000,3
C,3000,3
];

[QlikChick]

First of all, Ofzicoo, thank you SO much for persisting and stating that it should work! I actually don't have an excuse for what has happened, and would love your thoughts. I went in and added what you shared...and nothing happened. Same results, nothing new.  

Now here's the strange thing....

I noticed that my dimension for Week included a comment , it was something I tried early on but I decided I didn't need it and commented it out. So here is what was in the dimension:

=WeekOf

//=if(WeekOf>='11/17/2019',WeekOf,)

On a whim, I decided to go in an delete that comment, and everything then worked. HOW?? I have been commenting in the expression editor in all apps I've built without issue....why would this not work and when I delete out a comment from the expression editor it suddenly work? I had to call a colleague and demonstrate because I could not believe it. 

I can only assume this is either a glitch, or I should never ever be commenting in the expression editor and I just did not know that (self-taught). Any insight? 

Thanks so much for all the help! 

[Oliver_F]

Hi,

I guess that when you add total <WeekOf> Qlik excludes a Dimension that is called "WeekOf" but Your Dimension is "WeekOf //Comment". I know this sounds stupid first, but you could have Dimensions like

=if(Dim1 = 'A', Dim2, Dim3)

So do do not end up neither with Dim2 nor with Dim3. You end up with a dynamic dimension. So total <dim1> would not work.

I guess this is what happens here. Adding a Comment makes your dimension an expression which makes it a dynamic dimensions. Probably dynamic dimensions are not supported as a paramter.

I am not 100% sure about this, but this theory explains pretty much everything in your usecase.