topic Re: Waterfall chart using a stacked bar chart in QlikView

Waterfall chart using a stacked bar chart

Tue, 23 Aug 2016 15:47:16 GMT

Hi,

I need to build a waterfall chart using a stacked bar chart that shows me the evolution of two categories of sales of the last 5 months until the YTD for the qualified and requalified categories. The objective is to achieve a result like showed in the image below.

I’m trying to use the offset option with a stacked bar chart but I’m not getting any results. I have two expressions one for each category of sales and a offset expression based on something I read in Qlik Community.

I’m using as calculated dimension the following:

=Pick(Type, Month, 'Total')

The Type value is a table I have in load script:

Table:
LOAD * Inline [
Type
1
2
];

The formulas I’m using are:

=If(Pick(Type, Month, 'Total') = 'Total', Sum(total Value), Sum({$<CategoryType={'Qualified'}>} Value))

=If(Pick(Type, Month, 'Total') = 'Total', Sum(total Value), Sum({$<CategoryType={'Requalified'}>} Value))

The offset expression is:

=If(RowNo() = 1, 0, Sum(Total Value) - RangeSum(Above(If(Pick(Type, Month, 'Total') = 'Total', 0, Sum({$<CategoryType ={'Qualified'}>} Value)), 0, RowNo())))

Could anyone help me with this, please?

Thanks in advance

Regards

Paulo

Re: Waterfall chart using a stacked bar chart

Anil_Babu_Samineni — Tue, 23 Aug 2016 17:31:35 GMT

Interesting,

Can you post your application

Re: Waterfall chart using a stacked bar chart

sunny_talwar — Tue, 23 Aug 2016 18:19:57 GMT

I agree with Anil, it would be easier to help if you can provide what you have today.

Re: Waterfall chart using a stacked bar chart

Tue, 23 Aug 2016 21:46:12 GMT

Hi,

The image shared is not in Qlikview and is just to give a clue for the objective I want to achieve.

Pfa the sample application I'm working on.

Thanks

Paulo

Re: Waterfall chart using a stacked bar chart

sunny_talwar — Tue, 23 Aug 2016 22:10:08 GMT

Like this?

Re: Waterfall chart using a stacked bar chart

Wed, 24 Aug 2016 09:25:32 GMT

Hi Sunny,

Yes, It's exactly what I want, but only for the last 5 months until YTD. E.g March 16, April 16, May 16, June 16, July 16, YTD 16. Could you help?

Many Thanks

Paulo

Re: Waterfall chart using a stacked bar chart

sunny_talwar — Wed, 24 Aug 2016 10:25:19 GMT

This?

Re: Waterfall chart using a stacked bar chart

sunny_talwar — Wed, 24 Aug 2016 10:30:55 GMT

Or are you looking for this?

Re: Waterfall chart using a stacked bar chart

Wed, 24 Aug 2016 12:37:21 GMT

Hi Sunny,

Great. That's what I looking for.

However when I'm do it in my application I'not getting any data. Gives me 'No data to display'. I believe this is happen because the Date condition to restrict to the last 5 months.

Thanks again

Paulo

Re: Waterfall chart using a stacked bar chart

sunny_talwar — Wed, 24 Aug 2016 15:20:04 GMT

What is the expression you are using? Can you share that?

Re: Waterfall chart using a stacked bar chart

Wed, 24 Aug 2016 15:52:50 GMT

Hi,

I'm using the expressions that you share in sample.

=Sum({$<TipoCandidatura={'Qualificação'}>}NrCandidaturasEntradas) * Avg({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -4), 'DD-MM-YYYY') & '<=' & Date(Max(Date), 'DD-MM-YYYY'))"}>} 1)

and for the bar offset:

=If(Pick(Type, CategoryMonth, 'Total') = 'Total', 0,

RangeSum(Above(Sum({$<TipoCandidatura={'Qualificação', 'Requalificação'}>}NrCandidaturasEntradas), 1, RowNo())))

Re: Waterfall chart using a stacked bar chart

Wed, 24 Aug 2016 16:13:44 GMT

Hi sunny,

Based on the expressions from the last sample you share, I get the correct results, but the graph sow me all months ( Jan to Dec). The question now is how to show only the last 5 months.

Thanks

Paulo

Re: Waterfall chart using a stacked bar chart

sunny_talwar — Wed, 24 Aug 2016 19:40:23 GMT

I thought it was working? It stopped working again?

Re: Waterfall chart using a stacked bar chart

Thu, 25 Aug 2016 10:13:29 GMT

It works nice for all months. But I need to present only the last five months and its what is not working.

Please see a screenshot:

Any ideas what could be?

Thanks

Paulo

Re: Waterfall chart using a stacked bar chart

sunny_talwar — Thu, 25 Aug 2016 12:54:47 GMT

Multiplying with this -> Avg({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -4), 'DD-MM-YYYY') & '<=' & Date(Max(Date), 'DD-MM-YYYY'))"}>} 1) did not help?

Re: Waterfall chart using a stacked bar chart

Thu, 25 Aug 2016 14:58:56 GMT

Hi Sunny,

I think now its working fine.

Many thanks

Regards

Paulo

Re: Waterfall chart using a stacked bar chart

sunny_talwar — Thu, 25 Aug 2016 16:09:17 GMT

What was the issue?

Re: Waterfall chart using a stacked bar chart

Thu, 25 Aug 2016 16:21:54 GMT

I have made a change on the calculated dimension to add the date format

=Pick(Type, date(SubcategoriaMonthYear,'MMM-yyyy'), 'Total')

and put a dimension limit to 6 to show me only the last months.

BTW Sunny, I have now an issue, following the same case, to get the totals for the last 5 months, but this time using Text Objects for the totals and Labels for the Month Year description. Something like showed in the image below:

How could be the best approach to do this? Can you help me?

Many thanks

Paulo

Re: Waterfall chart using a stacked bar chart

sunny_talwar — Thu, 25 Aug 2016 17:11:46 GMT

So can you clarify as to what exactly are you trying to do?

Re: Waterfall chart using a stacked bar chart

Fri, 26 Aug 2016 09:00:05 GMT

Imagine the same exercise that we did above using a waterfall chart, now to be done using Text objects that will present the totals for each month of the last 5 and the total for YTD. E.g:

Text box 1 will have the total for Month-5 and a label with the description "March 2016"

Text box 2 will have the total for Month-4 and a label with the description "April 2016"

Text box 3 will have the total for Month-3 and a label with the description "May 2016"

Text box 4 will have the total for Month-2 and a label with the description "June 2016"

Text box 5 will have the total for Month-1 and a label with the description "July 2016"

Text box 6 will have the total YTD and a label with the description "YTD 2016"

I'm doing the same exercise for "Years" and it works fine with the following expression (e.g. for Year-2):

=num(sum({Year = {$(=max(Year-2))}, Month = {"<=$(=max({<Year={$(=max(Year-2))}>} Month))"}>} Value))

But when I trying to change the expression for Months is not working.

Also using the other expression like the following is not working.

=Sum({<Date = {'>=$(=MonthStart(Max(Date), -2)) <= $(=MonthEnd(Max(Date), -2))'}>}Value)

You could use the sample that I sent you before to see the data.

Thanks

Regards

Paulo