topic Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped? in QlikView

How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

neena123 — Mon, 17 Feb 2014 14:48:39 GMT

I am trying to create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped? Currently I am trying to achieve it by using this:

Gross Sales

This month:

Sum({$<[Ship Year]={$(=Year(today()))},[Ship Month]={$(=Month(today()))}>}[Gross Sales Amount])

Last Year same month:

Sum({$<[Ship Year]={$(=Year(today-1)))},[Ship Month]={$(=Month(today()))}>}[Gross Sales Amount])

Orders Shipped

(distinct if([Status]='Shipped',[Unique Order Key],null()))

The problem with those expressions is that it only lets me see the current status for February if I click on any other month the bar chart says "No Data To Display". How do I fix this?

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

giakoum — Mon, 17 Feb 2014 15:04:06 GMT

the following is definitely wrong as it will show your year for yesterday. Amend it as follows :

Sum({$<[Ship Year]={$(=Year(today())-1)},[Ship Month]={$(=Month(today()))}>}[Gross Sales Amount])

posting a sample application would help

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

MK_QSL — Mon, 17 Feb 2014 15:10:17 GMT

This will happen because you have fixed the Month as February.

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

Peter_Cammaert — Mon, 17 Feb 2014 15:13:33 GMT

Change the set identifier from $ into 1. $ takes into account all selections, even your month selection. But that depends on where you select a month. As Ioannis suggested, it would be good to have a sample document.

Best,

Peter

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

neena123 — Mon, 17 Feb 2014 15:14:00 GMT

I want the document to open up like this. For it to open with the current month selected however if I wish to see other months for example January's month sales for this year and last year it tells me "No Data to Display"

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

neena123 — Mon, 17 Feb 2014 15:15:09 GMT

Ohhh ok Thanks! I am new to QlikView.

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

MK_QSL — Mon, 17 Feb 2014 15:20:33 GMT

This can be achieved by removing Month Field from your both expressions..

Something like below...

Sum({$<[Ship Year]={'$(=Year(today()))'}>}[Gross Sales Amount])

Try this... hope it will help you.

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

Mon, 17 Feb 2014 15:21:11 GMT

It's only showing February data because you are limiting the data set with you set analysis expression i.e. sum({<...[Ship Month] = {$(=Month(Today()))}>}...

What would you like it to show when a user selects a specific month? If you want to to show the corresponding month's data, it might be better to use the Max() function in set analysis. Try replacing your Year(Today()) and Month(Today()) functions with Max([ShipYear]) and Max([Ship Month]). Now, when a user selects a month other than the most recent month, it will show that month's data.

On a side note, as Ioannis pointed out, your previous year set analysis expression needs to be corrected to [Ship Year] = {$(=Year(Today()-1))} (don't put the subtraction inside the Today() function).

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

Peter_Cammaert — Mon, 17 Feb 2014 15:29:55 GMT

That's something different.

Your dimension is a field containing MonthYear combinations. In every set analysis, set this field to Nothing, e.g. [Ship MonthYear] =, [Ship Year] = ...

Then replace the $(=Month(today())) part in your set analysis with the following: $(=Max([Ship Month])). Ship Month will now be set to the maximum available value from the list, instead of the fixed value of todays month.

Peter

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

neena123 — Mon, 17 Feb 2014 15:32:28 GMT

*I added your expressions but I realized I need to stop fixing February. But it still doesn't do what I want.

This shows this year's current month gross sales but when I try to add last year same month it does this:

When I want it to do this:

Re: How do I create a bar chart that shows current month gross sales amount, orders shipped and last year same month gross sales amount and orders shipped?

Mon, 17 Feb 2014 15:37:10 GMT

Use the max() function. Year(Max([Ship Year])) and Month(Max([Ship Month]))