Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
skoppe
New Contributor II

total previous quarters - offset in bar chart

Hi developers,

I have a question, example application attached.

question bar chart.PNG

I really hope that someone can help me and knows the solution. Thanks in advance!!!

Regards,

Stefan

Tags (2)
1 Solution

Accepted Solutions

Re: total previous quarters - offset in bar chart

Is this what you want to get?

Capture.PNG

Expression

RangeSum(Above(Sum({<Year=, Quarter=, QuarterYear=, Month=, Week=, Date=, RolID={1}>}UserCounter), 0, RowNo())) * Avg({<Year=, Quarter=, Month=, QuarterYear=, Week=, Date={'>=$(=QuarterStart(Max(Date), -7))<=$(=Date(Max(Date)))'},RolID={1}>}1)

8 Replies

Re: total previous quarters - offset in bar chart

Is this what you want to get?

Capture.PNG

Expression

RangeSum(Above(Sum({<Year=, Quarter=, QuarterYear=, Month=, Week=, Date=, RolID={1}>}UserCounter), 0, RowNo())) * Avg({<Year=, Quarter=, Month=, QuarterYear=, Week=, Date={'>=$(=QuarterStart(Max(Date), -7))<=$(=Date(Max(Date)))'},RolID={1}>}1)

skoppe
New Contributor II

Re: total previous quarters - offset in bar chart

Sunny T, you're the man! Thanks a lot!

regards,

Stefan

vinieme12
Esteemed Contributor II

Re: total previous quarters - offset in bar chart

not sure  do you just need this?

=RangeSum(Above(Sum({<Year=, Quarter=, QuarterYear=, Month=, Week=, Date=, RolID={1}>}UserCounter), 0, RowNo()))

rangesum.PNG

skoppe
New Contributor II

Re: total previous quarters - offset in bar chart

Thanks Vineeth, that is part of the trick! Sunny T used that also.

What I wanted is Sunny T's solution. Thanks again!

regards Stefan

skoppe
New Contributor II

Re: total previous quarters - offset in bar chart

Hi Sunny,

I've reviewed your solution, trying to understand it.

Why do you use the second part?

* Avg({<Year=, Quarter=, Month=, QuarterYear=, Week=, Date={'>=$(=QuarterStart(Max(Date), -7))<=$(=Date(Max(Date)))'},RolID={1}>}1) always seems to give 1.

When I only use the first part of you expression it also works.

Am I missing something (important)?

Regards,

Stefan

skoppe
New Contributor II

Re: total previous quarters - offset in bar chart

Sorry now I get it, it's to keep the 8 rolling quarters. Nevermind.

Regards,

Stefan

Re: total previous quarters - offset in bar chart

If you remove the Avg(.... 1) part don't you see all the months?

Capture.PNG

The chart on the right doesn't use Avg(1) and you are seeing all the quarters and not just the 8 quarters? Do you wish to see all quarters?

Re: total previous quarters - offset in bar chart

Hahahaha yes, absolutely. Avg is only 1 for the 8 quarters and 0 otherwise.