topic Re: YTD for bar chart in QlikView

YTD for bar chart

retko1985 — Fri, 06 Oct 2017 12:02:41 GMT

Hello,

here is a problem I am facing, look at the provided QVW. (Inline Load)

I tried to make an expression, but it is not working. There are two problem.

1) When I select One Month and two years, I want to have 2 Bars - 2016 2 and 2017 2. Now I have bars for each year and each month till selected month.

2) numbers are incorrect, since 2017 2 has to have 40 (20 + 20).

Please help me.

Thank you.

Re: YTD for bar chart

YoussefBelloum — Fri, 06 Oct 2017 12:15:13 GMT

Hi,

just added an aggregation function by Year

Aggr(YOUR_EXPRESSION, Year)

Find attached the solution.

Hope it helps !

Re: YTD for bar chart

retko1985 — Fri, 06 Oct 2017 12:35:26 GMT

Hi, this works fine if I have one month Selected (And thank you for this). But is is not working when I have 2 Years and More than 1 Month selected. I want to have a bar for each Year/Month.

Like if I select Years 2016 and 2017 + Month 1 and 2 I want to see 4 bars.

Can you please help me with that.

Thank you very much!

Re: YTD for bar chart

YoussefBelloum — Fri, 06 Oct 2017 12:38:44 GMT

Yes, just add the Month in the aggregation.

find attached

Re: YTD for bar chart

sunny_talwar — Fri, 06 Oct 2017 12:42:32 GMT

May be this

Aggr(RangeSum(Above(Sum({<Year, Month>}Sales), 0, RowNo())), Year, Month)

Re: YTD for bar chart

retko1985 — Fri, 06 Oct 2017 12:46:13 GMT

Hi,

It displays fine, but numbers are incorrect.

Should be like this:

Thank you.

Re: YTD for bar chart

upaliwije — Sat, 07 Oct 2017 05:07:06 GMT

Hi Sunny

Can you elaborate me what the following part in expression do

RowNo()

Re: YTD for bar chart

pradosh_thakur — Sat, 07 Oct 2017 06:17:07 GMT

Rangesum will give you the sum for all listed value. The value will be provided by above. Which by definition will return you sum of sales from 0(current) to row no (the number of row above).

Rowno() returns the no of row you are in.

Regards

Pradosh

Re: YTD for bar chart

retko1985 — Mon, 09 Oct 2017 09:06:52 GMT

Hello,

thank you for this, but I kinda have an issue here. It works great in model example that I created, but when I use it in my application, it doesn't work, and it kinda gives me the headaches already .

I don't get it, I copied entire chart, changed just the name of the field, and it even don't give me the right number when I choose only January 2017. Weird is that the March is correct, and for January it is Original January sum + sum for March.

I guess you don't have the suggestions here?

Thanks..

Re: YTD for bar chart

pradosh_thakur — Mon, 09 Oct 2017 10:02:44 GMT

first of all you need to check your data how it is loaded. here jan is loaded first then feb and then march and so on .

may be in your app that is not the case.

try putting it in a straight table and see how it is getting loaded which one first and which one next. you can use the fast change option to covert it into a straight table too and check the loading sequence. may be filter it by year to see the exact sequence.It will surely match then way it is getting loaded to your chart . you can share the screen shot too.

regards

Pradosh

Re: YTD for bar chart

retko1985 — Mon, 09 Oct 2017 11:22:56 GMT

Hello,

Thank you. You are right, March is the 1 month if I sort by Load Order. How come? We load master calendar from database where it is sorted. Any suggestion how this can be fixed? Thank you very much!

Re: YTD for bar chart

sunny_talwar — Mon, 09 Oct 2017 11:31:47 GMT

Are you using QlikView 12 or above? If you are, then try this

Aggr(RangeSum(Above(Sum({<Year, Month>}Sales), 0, RowNo())), Year, (Month, (NUMERIC)))

Re: YTD for bar chart

retko1985 — Mon, 09 Oct 2017 12:01:04 GMT

No, unfortunately 11.

Thank you.

Re: YTD for bar chart

sunny_talwar — Mon, 09 Oct 2017 12:05:40 GMT

Then may be you sort the Month field in the script... Something like this

FactTemp:

LOAD Year,

Month,

Year*100 + Month as YearMonth

Resident Fact

Order By Year, Month;

And then use this expression

Aggr(RangeSum(Above(Sum({<Year, Month>}Sales), 0, RowNo())), Year, YearMonth)