Stacked Bar Chart Issue

[#1]

Attached is the sketch of the required bar chart. Along with the sample Excel file.

Looking for help in building this chart

[#2]

How can I use your expression

=Sum(Amount)/Sum(TOTAL <Year> Amount)

with Set analysis, for example if I want to use the above expression for STATE field

[sunny_talwar]

So instead of Sales Year, you want to use STATE? or use STATE in place of Year field?

[#4 · Accepted solution]

no what I meant is lets say If I have another field called State and I want to use the same metrics for the filed of  'GA' for example

Something like

=Sum({<State={'GA'}>} Amount)

/

Sum({< State ={'GA'}> } TOTAL <Year> Amount)

its not working??

[sunny_talwar]

When you say it is not working, is it through errors? Unexpected result? Null? Please provide more detail as the expression itself looks good to me.

[#6]

It says in the caption " Error in Expression" and "Allocated memory exceeded"

[#7]

I got it, it works

Thanks for all your help

[sunny_talwar]

Can you share how the data looks? what the chart dimensions are and the expression used?

[Anonymous]

Hi Rehan,

If you found a successful solution, please take a moment to post your final results, and to mark a reply as Correct, as this marks the entire thread as Answered.

Thank you,

Qlik Community Team

[#10]

I thought I resolved it, but the sample I gave you has both Sale Year and Year in the same table, but in my original data set they are in different tables. So I created a variable. So in the sample data the dimensions were Year, Sale Year

which was 

If(Year([Sale Date]) >= Year, '<1 Year',

       If(Year([Sale Date]) >= ((Year)-2) and  Year([Sale Date]) < Year,'Prior 2 Years',

       If(Year([Sale Date]) <= ((Year)-3),'< 3 Years')))  as [Sales Year] in the script

In  my original data set now, I put all the above in a variable and used that variable as a dimension for [Sale Year]

vSaleYear

If(Year([Sale Date]) >= Year, '<1 Year',

       If(Year([Sale Date]) >= ((Year)-2) and  Year([Sale Date]) < Year,'Prior 2 Years',

       If(Year([Sale Date]) <= ((Year)-3),'< 3 Years'))) and used that in my Bar Chart but I get 100% now for each Year  because the values Sum(Amount) and Sum(Total <Year> Amount) are the same.

The same logic works perfectly fine, if I put that logic in the script like we did earlier.

Am I missing something here?

[sunny_talwar]

It is very very difficult to troubleshoot a problem by just looking at an expression. I know there are people who can do it, but I don't have that talent my friend. Either provide more details, or I would hope someone else might be able to help you better