Selecting Dates from date field

[fatihu]

Hi Community,

i have the following data:

id	yearMonth	sales
1	2023-03	81€
2	2023-04	90€
3	2022-08	32€
...	...	...

Now I want to create some measurements in specific "date ranges". I have to use field yearMonth to create that measurements. I need the following:

1. all sales in current month
2. all sales current year (january to current month)
3. all sales last year

How can I (first) define the date ranges? Can someone help me please?

Thanks!

[Michael_Tarallo]

Hello Faithu - so from what I see here - you do not have an actual Data field - such as 04/17/2023? - you only have a column YearMonth? Is this correct?

If that is the case you will have to employee certain date functions and possibly use Set Analysis to extrapolate the values. 

For example this is current month:

Sum({<[Order Date.autoCalendar.YearMonth]={"$(=Year(Today()) & '-' & Month(Today()))"}>}[Sales])

This is last month:

Sum({<Indicator={'Y'},[Order Date.autoCalendar.YearMonth]={"$(=Year(Today()) & '-' & Month(MonthStart(Today(),-1)))"}>}[PartSales])

(notice the -1 in the Today expression)

This is an expressions which Sums the measure (field) Sales - where the YearMonth dimension (field) equals today's month and today's year - they are formatted and concatenated basically to match the value in the data. If you have real dates in your data - it is much easier to perform time period analysis as we have tools and an engine that automatically do stuff like that. 

in case you are not familiar with set analysis - here are some resources  that can help:

• introduction to Set Analysis: - Part 1 https://www.youtube.com/watch?v=YMQJnKMkfxg 
• Cool Stuff you can so with Set Analysis - Part 2 https://www.youtube.com/watch?v=nB-HJ8X1QbI&t=0s 
• Qlik Sense in 60 - Set Analysis https://www.youtube.com/watch?v=uVDOESAxp3M 

Let me know if this helps.