Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Karthick30
Creator
Creator

Monthstart and Monthend function

Hi ,

I have 2 columns, which is Date and value. Date is from 05/01/2020(May 1st 2020) till today. . I am creating a line chart which has MMM YYYY (Date) as dimension. For each month, i have to calculate exact growth value as Measure.

For example , if 05/01/2020 has value 100 and 05/31/2020 has 150 means , I need to show 50 ( 150 -100 )(monthend value - monthstart value) for May 2020. likewise for all months

Here the problem is ,1) certain month start/month end  value has null values. I need to ignore that and take next day value. suppose if 06/01/2020 has null means, I need to check for 06/02/2020 as monthstart. Same logic for month end as well(need to take previous date value, if month end value is null means)

2)For current month(May 2022), 05/01/2022 as monthstart and since 05/23/2022 as last value in load , I need to use value for 05/23/2022 - value for 05/01/2022 

Any help/logic on this please

Labels (4)
1 Reply
MarcoWedel

having month as dimension this measure expression might work:

 

FirstSortedValue(Value,-Date)-FirstSortedValue(Value,Date)

 

 

 see also:

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Basic...