Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Would you be able to share a sample to look at what you have?
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.
What is vEBIT? Use set analysis to show last year's value
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)
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
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?
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.
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
Thank you it works