Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

timorbit
New Contributor III

Horizontal Bar Chart Display Actual vs. Previous Month

Hi Folks,

I want to create a stacked horizontal bar chart to compare either (1) acutal vs. previous month sales or (2) actual vs. forecast sales.

My approach is to create a ValueList with my relevant KPIs as my Dimension, e.g. =ValueList('EBIT','EBTA','TEST').


And one meassure like this:

if(ValueList('EBIT','EBTA','TEST')= 'EBIT',$(vEBIT),

if(ValueList('EBIT','EBTA','TEST')= 'EBTA',$(vEBTA),

if(ValueList('EBIT','EBTA','TEST')= 'TEST',4,NULL())))

I am aware that I must create a second meassure in this bar chart to implement the comparing figures, like (1) or (2) you can see at the top of this post.

But I have problems with the syntax. I like to implement the following idea:

if(ValueList('EBIT','EBTA','TEST')= 'EBIT',$(vEBIT), Month(addmonths(max(Date) ,- 1)),

if(ValueList('EBIT','EBTA','TEST')= 'EBTA',$(vEBTA), Month(addmonths(max(Date) ,- 1)),

if(ValueList('EBIT','EBTA','TEST')= 'TEST',4,NULL())))

You can see, that I want to implement the expression Month(addmonths(max(Date) ,- 1)), but I think on the wrong place and I think in the wrong way.

I have problems. I am happy about your help and maybe an easier way instead of using a ValueList.

Thanks,

Regards, Tim

1 Solution

Accepted Solutions
dwforest
Valued Contributor

Re: Horizontal Bar Chart Display Actual vs. Previous Month

I you just want the values sliced by year/month in a bar graph, no set expression is probably required, just set Month and Year as X dimensions and Qlik will sum(Sales) accordingly

9 Replies

Re: Horizontal Bar Chart Display Actual vs. Previous Month

Would you be able to share a sample to look at what you have?

timorbit
New Contributor III

Re: Horizontal Bar Chart Display Actual vs. Previous Month

I want something like this: Red should be the previous year and blue the actual year. Sorry but I have no Idea how I can explain it more detailed. Thank you for your post.

Re: Horizontal Bar Chart Display Actual vs. Previous Month

What is vEBIT? Use set analysis to show last year's value

dwforest
Valued Contributor

Re: Horizontal Bar Chart Display Actual vs. Previous Month

As Sunny says Set analysis, so, yes your formula is in the wrong place, it should vEBIT.

Where vEBIT is something like

Sum({$<SalesID={"=(SalesDate<=$(=Month(addmonths(max(Date) ,- 1))))"}>} Sales)

timorbit
New Contributor III

Re: Horizontal Bar Chart Display Actual vs. Previous Month

there is my problem, I have already read in the forum, but it would be nice if you can tell me how you think that.

In the attachment you can see my target. I want to confront the previous year with the actual year (This example is from a football magazine). I like this chart.


The lines on the left of the graph are not opportunities, scores and fouls, they are a variety of KPIs

In the one case the left (previous year) balk is longer and maybe in the other case the right. I am pleased about your help


timorbit
New Contributor III

Re: Horizontal Bar Chart Display Actual vs. Previous Month

‌Thanks, that means, the printed fat one I should take 1: 1 and just have to adjust the salesID, SalesDate and Sales? With the fat scripture I get the last year?

timorbit
New Contributor III

Re: Horizontal Bar Chart Display Actual vs. Previous Month

I tried something like this:

Actual Month March 2018

Sum({$ < vActMonat ={"$(=(AddMonths(Date)))"}>} {<Sales>}VALUE)

March 2017

Sum({$ < vActMonat ={"$(=(AddMonths(Date,-12)))"}>} {<Sales>}VALUE)

The same restrained is, when I implement the following expressions:

Sum({$<Year={$(=Max(Year))}>}Sales)

Sum({$<Year={$(=Max(Year)-1)}>}Sales)

e.g.

2018

=sum({<year={"<=$(vActJahr) >=$(=$(vActJahr)-1)"},month={"=$(vActMonat)"}>}Sales)

2017

sum({<year={"<=$(vActJahr)-1 >=$(=$(vActJahr)-2)"},month={"=$(=vActMonat)"}>}Sales)

2016

sum({<year={"<=$(vActJahr)-2 >=$(=$(vActJahr)-3)"},month={"=$(=vActMonat)"}>}Sales)

I get the same Value in each direct comparison like the following screenshot. I convert the chart in vertical, for an easier comprehension. I really nead support, Thanks.

dwforest
Valued Contributor

Re: Horizontal Bar Chart Display Actual vs. Previous Month

I you just want the values sliced by year/month in a bar graph, no set expression is probably required, just set Month and Year as X dimensions and Qlik will sum(Sales) accordingly

timorbit
New Contributor III

Re: Horizontal Bar Chart Display Actual vs. Previous Month

Thank you it works

Community Browser