Bar Chart: Actual Sales for last 12 months by Product Category

[Steve_Remington]

Do you ever have one of those days when you think you are not smart enough to use a product?

I have a loaded the following data set from a MySQL database into a Qlik Sense App. The data set has the following dimensions and measures:

• [Order Month Date] - This date has the first day of the month for each month (e.g. 01/01/2019, 01/02/2019, etc.)
• [Product Category] - e.g. Cars, Trains, Ships, etc.
• [Country] - e.g. Spain, Norway, USA, etc.
• [Actual Extended Price] - A numeric value
• [Actual Shipping Cost] - A numeric value
• [Actual Sale Amount] - A numeric value

The data set has about 2.5 years of data back from the current month.

I would like to create a simple vertical bar chart that has [Product Category] on the horizontal axis and sum([Actual Sale Amount]) for the last 12 months on the vertical axis. The objective is to have the chart always show the per category sale amount for the last 12 months from the current month.

I assume the measure value has to be calculated using set analysis but I must admit I am struggling with the what appears to me the somewhat cryptic set analysis syntax used by Qlik Sense. I have tried many different examples from this forum but none of them work.

In pseudo code terms, I want to achieve the following:

The sum of [Actual Sale Amount] where [Order Month] is >= AddMonths(Max([Order Month]), -11) and <= Max([Order Month])

I would very much appreciate if a forum member could suggest the correct syntax for the sum function with set analysis.

Thanks in advance to all who reply.

[YoussefBelloum]

Hi,

the below expression will give you your rolling year starting from the date you select, or a rolling year starting from the max date of your data if don't select a Date:

=sum({<[Order Month Date]={"$(=AddMonths(max([Order Month Date]),-11))"}>}[Actual Sale Amount])

[Channa]

=sum({<[Order Month Date]={"$(=AddMonths(max([Order Month Date]),-11))"}>}[Actual Sale Amount])

it will bring only one month

[Channa]

=sum({<[Order Month Date]={">=$(MonthStaty(AddMonths(max([Order Month Date]),-11)))"}>}[Actual Sale Amount])

[Steve_Remington]

Hi Youssef,

Thanks for the reply. I copied your suggested function into the fx for the bar chart measure and when I viewed the chart it showed no values at all. See attached file.

Also, when reading your suggested function it appears to me that the set is for a fixed value of the Max Order Month Date - 11 months and not a range. Did I read the set function correctly?

[YoussefBelloum]

My mistake, Channa's reply is correct.. I forgot the >= part. give it a try

[Steve_Remington]

Hi Youssef,

I tried Channa's reply (I fixed the little typo in the "MonthStart" function name) and the chart is populated but it is populated with values that are equal to just using sum([Actual Sale Amount]) (i.e. to set filter applied).

[Channa]

ok

=sum({<[Order Month Date]={">=$(MonthStat(AddMonths(max([Order Month Date]),-11))) <=$(MonthEND(AddMonths(max([Order Month Date]),0))) "}>}[Actual Sale Amount])

[YoussefBelloum]

Actually no need to use Monthstart before Addmonths..

use this:

=sum({<[Order Month Date]={">=$(=AddMonths(max([Order Month Date]),-11))"}>}[Actual Sale Amount])

[Steve_Remington]

Hi Yuossef,

That formula works on the bar chart in an app whose data source is the Excel spreadsheet I attached to the original message.

However, if I copy the same formula to the fx of the chart in another app whose data set with exactly the same structure is from a MySQL database, the chart is blank. Do you have any idea why the difference?

Attached is a copy of the QVF file with data sources from the MySQL database.