topic Re: Stacked Bar Chart Issue in QlikView

Stacked Bar Chart Issue

Thu, 16 Jun 2016 01:39:01 GMT

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

Looking for help in building this chart

Re: Stacked Bar Chart Issue

sunny_talwar — Thu, 16 Jun 2016 01:48:41 GMT

I guess create a new field from Sales Date. -> Year([Sales Date]) as [Sales Year]

And then use Year as your 1st dimension and Sales Year as second dimension

Expression:

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

Re: Stacked Bar Chart Issue

Thu, 16 Jun 2016 02:16:14 GMT

But if you look at the sketch I need to group the previous 2 years as one block and anything older then 3 yrs as a seperate block

Re: Stacked Bar Chart Issue

sunny_talwar — Thu, 16 Jun 2016 02:29:33 GMT

May be create a flag in the script:

If(Year = Year([Sales Date]) or Year = Year([Sales Date]) + 1, Year([Sales Date]), Dual('<=' & (Year -2), (Year - 2))) as [Sales Year]

And then then same two dimensions and try the same expression

Re: Stacked Bar Chart Issue

Thu, 16 Jun 2016 02:31:09 GMT

Can you please post a sample qvw

Re: Stacked Bar Chart Issue

sunny_talwar — Thu, 16 Jun 2016 03:58:56 GMT

Like this:

Re: Stacked Bar Chart Issue

Thu, 16 Jun 2016 13:21:56 GMT

Yes exactly. can you pls post the QVW?

Re: Stacked Bar Chart Issue

sunny_talwar — Thu, 16 Jun 2016 13:53:08 GMT

I did add it to my response above. Open the link here ->Re: Stacked Bar Chart Issue

Re: Stacked Bar Chart Issue

Thu, 16 Jun 2016 14:29:34 GMT

Got it, one last question. I am still not able to do the 3 stacks, so if Year is 2016 I would like to see

Stack 1 Sale Year = Year

Stack 2 Sale Year = Year-1 and Year -2

Stack 3 Sale Year <= Year -3

All 3 stacks should add up to 100%

Re: Stacked Bar Chart Issue

sunny_talwar — Thu, 16 Jun 2016 15:20:28 GMT

Above image is showing three stacks and they are adding up to 100%. Did you follow the similar logic in the script?

Re: Stacked Bar Chart Issue

Thu, 16 Jun 2016 17:23:11 GMT

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

Re: Stacked Bar Chart Issue

sunny_talwar — Thu, 16 Jun 2016 18:16:45 GMT

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

Re: Stacked Bar Chart Issue

Thu, 16 Jun 2016 18:26:45 GMT

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

Re: Stacked Bar Chart Issue

sunny_talwar — Thu, 16 Jun 2016 18:31:43 GMT

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.

Re: Stacked Bar Chart Issue

Thu, 16 Jun 2016 18:36:55 GMT

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

Re: Stacked Bar Chart Issue

Thu, 16 Jun 2016 18:58:16 GMT

I got it, it works

Thanks for all your help

Re: Stacked Bar Chart Issue

sunny_talwar — Thu, 16 Jun 2016 18:58:45 GMT

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

Re: Stacked Bar Chart Issue

Anonymous — Thu, 16 Jun 2016 19:06:23 GMT

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

Re: Stacked Bar Chart Issue

Fri, 17 Jun 2016 03:51:51 GMT

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?

Re: Stacked Bar Chart Issue

sunny_talwar — Fri, 17 Jun 2016 11:55:26 GMT

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