Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
AVER88
Contributor II
Contributor II

Barchart with only the last 4 months of data

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

Labels (3)
1 Solution

Accepted Solutions
Vegar
Partner
Partner

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. 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

7 Replies
Vegar
Partner
Partner

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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
AVER88
Contributor II
Contributor II
Author

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
Partner
Partner

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])

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
AVER88
Contributor II
Contributor II
Author

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
Partner
Partner

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. 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Vegar
Partner
Partner

See attached qvf for an inline source example.

 
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
AVER88
Contributor II
Contributor II
Author

Thank you so much Vegar, it worked!