Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)
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]))
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])
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]
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.
Thank you so much Vegar, it worked!