Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Bar Chart last 6 months trend

hey guys....Probably a very simple problem but I've been stuck on this for quite a while.

I have a guage chart object that displays the profit percentage for the selected month (list box object) and selected year (list box object).

Underneath this, I wanted to display a bar chart that presents the profit percentage for the last 6 months from the selected month. For eg: If I select July 2009, guage chart displays profit percentage for July and the bar chart should display the profit percentage for the months - Jan, Feb, Mar....Jun.

Any ideas please?

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

I'd handle it like this (untested):

,date(monthstart(Date),'MMM YY') as Month
,date(yearstart(Date),'YYYY') as Year

sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Sales)
/ sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Cost) - 1

View solution in original post

9 Replies
johnw
Champion III
Champion III

I'd handle it like this (untested):

,date(monthstart(Date),'MMM YY') as Month
,date(yearstart(Date),'YYYY') as Year

sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Sales)
/ sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Cost) - 1

Not applicable
Author

John,

This worked out perfectly. It is exactly what I was looking for.

Thanks very much for your help!

Not applicable
Author

Dear Quote,

I would like to attach similar bar chart into my analysis, but because I am on a begginer level I have no idea where to use expression that John adviced. Could you describe me in details how it works?

Thank you in advance,

Beata

johnw
Champion III
Champion III


beciafreestyle wrote:I would like to attach similar bar chart into my analysis, but because I am on a begginer level I have no idea where to use expression that John adviced. Could you describe me in details how it works?

Sure. The first two lines:

,date(monthstart(Date),'MMM YY') as Month
,date(yearstart(Date),'YYYY') as Year

Belong in the script. I would typically load these to a separate Calendar table, but you could also load them wherever you're loading your Date field. These define the Month and Year fields as the first day of the month, and the first day of the year. But they only display the month and year, and the year respectively. I like defining my Month and Year as actual dates, because it allows me to use all of the available date functions on them. And that's part of what we're about to do with this expression:

sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Sales)
/ sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Cost) - 1

In this case, you would make a bar chart with Month as the dimension. Then you would enter this whole thing as the expression on the expression tab. Since both the first and second line of this are nearly identical, let's just talk about the first line.

The core of what we're doing on that line is sum(Sales). We want the sum of some sales. But since we've selected a specific month (according to the original post), a simple sum(Sales) would only return the sum of the sales for that specific month. Instead, we want the sum of sales for the past six months. One way to accomplish that is using set analysis. You can identify set anlysis by the curly brackets, {}. Everything within the curly brackets is our set analysis expression. By default, these expressions MODIFY the current set rather than starting over with a blank set. So we're starting with the set where the user has selected a specific Month. The "Date=,Year=," tells QlikView to ignore any selections in these two fields. Pretend nothing has been selected. While perhaps not strictly necessary, this is at least a safety measure since date fields are often selected in combination. You would want to list EVERY date field related to the date field you expect to be selected. In this case, we're assuming that the only related date fields are Date, Month and Year. Then we get to our Month expression. The curly brackets enclose the expression. The double quote indicates that we're building a search string. We're not giving it a list of values. Instead, we're giving it a search string, which will cause it to look for matching values. The general structure here is Month={">some month <=some other month"}. So we're trying to give it expressions for a date range that includes the last six months. Let's start with the second part since it's a little simpler. We want dates less than or equal to the selected month. You might think this would be <=Month. Alas, not quite so simple. First, I assume that perhaps no months are selected, or many months are selected. What to do then? I've decided to use max(Month), so the most recent of the selected months. If no Month is selected, this will be the most recent month available. Working our way out, we then say date(...,'MMM YY'). Set analysis basically does a text comparison instead of a numeric or date comparison. So we need to make sure that our date, max(Month), ends up in the same format as Month for comparison purposes. The date() expression tells it an explicit format to use so that our comparison will work. The only part that is different for the "greater than" portion of our search string is the addmonths() function. In this case, we're subtracting six months from the selected month. Then we do the same thing with Cost to get the cost for the past six months. Divide, subtract one, and you have a six month margin as desired.

It is quite possible that your problem will have a MUCH simpler solution than this, though. Let's say you want a bar chart for the six-month rolling average of the sales. That is trivial to do in QlikView. Create a new bar chart, use Month as your dimension, sum(Sales) as your expression, then look down at the bottom left of the Expressions tab. There's an Accumulation section. Set the expression to "Accumulate 6 Steps Back". Done.

Not applicable
Author

Dear John,

Finally I understood set analysis! I really appreciate your help and I would like to thank you for such extensive answerSmile

I already managed to make MasterCalendar, so I didn't need to put Year and Month into script, but the rest was like black magic. Anyway I still have some problems though, for example- now I'm trying to attach mini chart into my table, and I would like it to show Sales for months:

sum ({<CalendarDate=,CalendarYear= {2009},CalendarMonth=>}InvoiceAmount) and it doesn't work. Can it be fault of Label? Or anything else?

Thank you in advance,

Best greetings,

Beata

Miguel_Angel_Baeyens

Let's say you want to show the sum of amounts in 2009, ordered by months. I'd add a new dimension, SalesDateMonth, so expression

sum ({< CalendarYear = {2009} >}InvoiceAmount)
would work right. Understand that a dimension is how you want to display your data (month by month in this case), and expression is what you want to display (amount of invoices).

Not applicable
Author

Great, I understood! I just changed dimension in the Settings of mini chart and it works fine!

Thank you for answers to my silly questions!

Have a nice day,

Beata

Not applicable
Author

Hi John,

Same scenario only the thing is my source column value is not date instead it is like below.

Calendarmonthyear:

JAN 2014

FEB 2014

MAR 2014

.

.

.

DEC 2014

how can i show every time last 6 months trend

phaneendradodda
Contributor
Contributor

Hello John,

I am new to Qlik sense ..

My issue is same .. Having MTD, if i select the MTD Gauge chat should show particular month value and Bar chart should show last 6 month values . i tried with your expression, but i din't get. Can you solve please.

Date(MonthStart(TempDate),'MMMYYYY') as Business_MonthYear ......... in Master calendar

My Gauge chart Expression is...

Count( { < [Business Area] = , Scorecard_Date= { “ >= $(vSelectedMonthStart  )< = $ (vSelectedDate) ” },

CTRCT_CDE_BR_Date_Src_Create = { “ < =$ (vSelectedDate) “ } ,

CTRCT_CDE_BR_Date_EFF_END = { “ >= $(vSelectedMonthStart  ) “ } ,

IDQ_Collibra_Date_Rule_End = { “ >= $(vSelectedMonthStart  ) “ } ,

IDQ_Collibra_Date_Rule_Create = { “ < =$ (vSelectedDate) “ }>} Distinct IDQ_Collibra_CDE_ID)

/

Count( { < [Business Area] = , CTRCT_CDE_BR_Date_Src_Create = { “ < =$ (vSelectedDate) “ } ,

CTRCT_CDE_BR_Date_EFF_END = { “ >= $(vSelectedMonthStart  ) “ } ,

Contracts_Business_Date = { “ >= $(vSelectedMonthStart  )< = $ (vSelectedDate) ” },

CDE_Status _Check = {“Accepted”, “Candidate”}>} Distinct CDE_ID)

 

in expression some variable is there related to MTD.

Thanks and Regards.