Horizontal Bar Chart Display Actual vs. Previous Month

[tim_m]

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

[sunny_talwar]

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

[tim_m]

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.

[sunny_talwar]

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

[dwforest]

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)

[tim_m]

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

[tim_m]

‌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?

[tim_m]

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 · Accepted solution]

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

[tim_m]

Thank you it works