Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhavvibudagam
Creator II
Creator II

How to restrict last 6 Months in Bar chart

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.

18 Replies
Anil_Babu_Samineni

If Data not there for Jan and Feb then what you do?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sudhirpkuwar
Partner - Creator II
Partner - Creator II

Thats whyI have written YearStart (Which will start from Jan till June)

Min(Date) might take even 2015 or 2016 data.....

sudhirpkuwar
Partner - Creator II
Partner - Creator II

Clearly mentioned in the post


"In the Year 2018 I have the Jan to Dec Months Data."

bhavvibudagam
Creator II
Creator II
Author

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.




sudhirpkuwar
Partner - Creator II
Partner - Creator II

Great.... A joint venture....

bhavvibudagam
Creator II
Creator II
Author

Hi,

But i have to show 2018 bars by default.

sudhirpkuwar
Partner - Creator II
Partner - Creator II

Make Year 2018 as default selection for that sheet

qlikviewwizard
Master II
Master II

Hi,

Try like this.

Dates in Set Analysis

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 )