topic Re: total previous quarters - offset in bar chart in QlikView

total previous quarters - offset in bar chart

skoppe — Fri, 23 Dec 2016 13:30:56 GMT

Hi developers,

I have a question, example application attached.

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

Regards,

Stefan

Re: total previous quarters - offset in bar chart

sunny_talwar — Fri, 23 Dec 2016 13:45:33 GMT

Is this what you want to get?

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)

Re: total previous quarters - offset in bar chart

skoppe — Fri, 23 Dec 2016 13:53:44 GMT

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

regards,

Stefan

Re: total previous quarters - offset in bar chart

vinieme12 — Fri, 23 Dec 2016 13:56:10 GMT

not sure do you just need this?

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

Re: total previous quarters - offset in bar chart

skoppe — Fri, 23 Dec 2016 14:02:14 GMT

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

Re: total previous quarters - offset in bar chart

skoppe — Fri, 23 Dec 2016 14:13:26 GMT

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

Re: total previous quarters - offset in bar chart

skoppe — Fri, 23 Dec 2016 14:15:12 GMT

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

Regards,

Stefan

Re: total previous quarters - offset in bar chart

sunny_talwar — Fri, 23 Dec 2016 14:16:41 GMT

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

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

sunny_talwar — Fri, 23 Dec 2016 14:17:13 GMT

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