Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me on below requirement.
I have a date field like below in DD/MM/YYYY format.
In the Year 2018 I have the Jan to Dec Months Data.
In one bar chart I have to show first 6 months data i.e., from jan to Jun only.
In another bar chart I have to show last 6 months data i.e., from July to Dec.
I have calculated Month Name as
MonthName(Date) as [Month Name]
Please help me to write the expressions for first 6months restriction and Last 6 motnhs restriction.
Thanks in advance.
If Data not there for Jan and Feb then what you do?
Yes, But clearly she written
Could you please help me to write first 6 months restriction formula
First 6 months - I believe first date should include
Thats whyI have written YearStart (Which will start from Jan till June)
Min(Date) might take even 2015 or 2016 data.....
Clearly mentioned in the post
"In the Year 2018 I have the Jan to Dec Months Data."
Hi Anil and Sudhir,
Thanks for your reply.
For last 6months anil's expression is working
=Sum({<DateField={">=$(=AddMonths(Max(DateField),-6)) <$(=Max(DateField))"}>}Sales)
For first 6months sudhir expression is working
=Sum({<DateField={">=$(=YearStart(Max(DateField))) <=$(=AddMonths(YearStart(Max(DateField)),6))"}>}Sales)
In My source I have 2022,2023 years as well getting results like below. When I have selected 2018 able to see the correct result. But by default I have to show 2018 first and last 6months data. Then after if user selects 2017 or any other year it has to show that months data. Please let me know how to show the result by 2018 by default.
Thanks in advance.
Great.... A joint venture....
Hi,
But i have to show 2018 bars by default.
Make Year 2018 as default selection for that sheet
Hi,
Try like this.
Script:
Date(MonthStart(Date),'MMM-YY') as Month,
Expressions:
Sum( {$<Month={"<=$(=Date(AddMonths(Max(Month),-6),'MMM-YY'))"}>} Amount )
Sum( {$<Month={">=$(=Date(AddMonths(Max(Month),-6),'MMM-YY'))"}>} Amount )