Barchart with only the last 4 months of data

[AVER88]

Hi everyone!

I am struggling with a pretty straight forward issue that I have in one of my barcharts that I am creating.

I have a Date-dimension and a "Car Types" dimension. I calculate the car-sales by having a measure in my bar chart
that is named "Account Name". So if you take a look at my screenshot you'll see that we sold 1 BMW in 2017-Sep, 1 BMW in 2017-Oct and so on. 
For Volkswagen we sold 28 cars in 2016-Apr, 23 Cars in 2016-May and so on.

I want to ONLY show sales for the last 4 months in my dataset containing data from 2018 to 2016. 

How do I create this calculation? Could I just create a Date-expression with this logic? Or should I use another way?
How would that expression be?

Thank you very much in advance,

Br
A.V

[Vegar]

Im assuming that your period is formated as an dual date/monthname value. If so then you can do something like this..

sum({<Period = {"<=$(=max(Period))>$(=num(addmonths(max(Pesriod),-4)))"}>}Qty)

[AVER88]

Hi Vegar!

Thanks for reaching out, though I do not seem to get it to work, maybe I am missing something.
Here is my expression for Date now;
=sum({<Date = {"<=$(=max(Date))>$(=num(addmonths(max(Date),-4)))"}>}Count([Account Name]))

I get an error that says: Error in expression: Nested aggregation not allowed
=sum({<Date ={" <= 2018-Jan>43001">}Count([Account Name]))

[Vegar]

The error message is correct, you can't do nested aggregations (without using the aggr) in Qlik Sense, but I don't think you'll need to worry about that.

You can skip the sum and replace it with your count like this. 

=count ({<Date = {"<=$(=max(Date))>$(=num(addmonths(max(Date),-4)))"}>}[Account Name])

[AVER88]

Hi again,

Still not getting it to work, is it because of my date-column perhaps?
This is one transformation i did in the LOAD-script on Date. 

Date(EVENT_DATE,'YYYY-MMM') as [Date]

[Vegar · Accepted solution]

Please look at these two expressions and my description in order to understand what you do and what you want to do.

Date(EVENT_DATE,'YYYY-MMM') as [Date]: Use the numeric value of the field EVENT_DATE in order to create a date value with the format 'YYYY-MMM'. The output should be named Date.

Date#(EVENT_DATE,'YYYY-MMM') as [Date]: Intepret the text in EVENT_DATE as a date. EVENT_DATE is stored in the source as text having the format 'YYYY-MMM'. The output should be named Date.

Do you notice the difference? In order for Date() to work you will need to have a value in date format. With Date# you are creating a date out of an string. 

[Vegar]

See attached qvf for an inline source example.

[AVER88]

Thank you so much Vegar, it worked!