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: 
Anonymous
Not applicable

Year comparison in bar chart

Hello,

I asked almost the same question . Sum of the same companies in different years

but now, I want to add something. I want to compare the same periods of these years.

For example,

Today is 28 February. And I want to compare the data from January 1 to February 28 in these years.

Sum({<Company = p({<Year = {"$(=Max(Year) - 1)"}>})>}Measure)

Sum of the same companies in different years

How this formula can be changed ?

Also,

I want to compare all the companies that are in these years(2016,2017,2018)  If I add another year, for example, 2015, it should compare only the companies which are in all these 4years)

I compare these periods -- Year( as dimension)

  Measure :=SUM({$<Date = {'>=$(=YearStart(Today()))<=$(=MonthEnd(Today()))'}>}FactSoldAmount)                                      Measure:=SUM({$<Date = {'>=$(=YearStart(AddYears(Today(),-1)))<=$(=MonthEnd(AddYears(Today(),-1)))'}>}FactSoldAmou         Measure:=SUM({$<Date = {'>=$(=YearStart(AddYears(Today(),-2)))<=$(=MonthEnd(AddYears(Today(),-2)))'}>}FactSoldAmount)

but I can't unite it with the formula that calculates the same companies in these years.

25 Replies
Anonymous
Not applicable
Author

I cant

sunny_talwar

Mock up data my friend...

Anonymous
Not applicable
Author

It's okay.

Can I use this formula in bar chart like this?

28309995_2064236076950054_2098739438_o.jpg

In January second chart should be current year and  first chart should be the previous and it will show the sum for the same companies. But in this case I have 3years and I want to show all of them in all months'. I also want to have drill down  on them. When I select January , all dates  of the january should be shown and every date should have the comparison chart to the previous date( as it was in months)

sunny_talwar

I believe it should work, but I am new to Qlik Sense too... may be omarbensalem‌ can offer his expertise here

Anonymous
Not applicable
Author

Sorry for disturbing you again, but can you tell me where should I add {1-$} in the formulas that you told me to ignore all current selections?

And thanks a lot !!! I really appreciate your help!!

sunny_talwar

In this one?

=Sum({$<Date = {">=$(=YearStart(Today()))<=$(=MonthEnd(Today()))"}>} FactSoldAmount)


=Sum({$<Date = {">=$(=YearStart(AddYears(Today(), -1)))<=$(=MonthEnd(AddYears(Today(), -1)))"}, Company = p({<Date = {">=$(=YearStart(Today()))<=$(=MonthEnd(Today()))"}>})>}FactSoldAmount)


=Sum({$<Date = {">=$(=YearStart(AddYears(Today(), -2)))<=$(=MonthEnd(AddYears(Today(), -2)))"}, Company = p({<Date = {">=$(=YearStart(Today()))<=$(=MonthEnd(Today()))"}>})>}FactSoldAmount)

May be here (I believe that just {1} should be enough not sure why you need {1-$}...

=Sum({1<Date = {">=$(=YearStart(Today()))<=$(=MonthEnd(Today()))"}>} FactSoldAmount)


=Sum({1<Date = {">=$(=YearStart(AddYears(Today(), -1)))<=$(=MonthEnd(AddYears(Today(), -1)))"}, Company = p({1<Date = {">=$(=YearStart(Today()))<=$(=MonthEnd(Today()))"}>})>}FactSoldAmount)


=Sum({1<Date = {">=$(=YearStart(AddYears(Today(), -2)))<=$(=MonthEnd(AddYears(Today(), -2)))"}, Company = p({1<Date = {">=$(=YearStart(Today()))<=$(=MonthEnd(Today()))"}>})>}FactSoldAmount)

Anonymous
Not applicable
Author

Yes , it works .And if I want to ignore only the year and month?  I tried to write {1} only before the first date  but it seems I'm incorrect

sunny_talwar

Replace 1 with <Year, Month>

Anonymous
Not applicable
Author

Hello  

I have one question regarding this issue. I want to have climber selection bar where I can see the Companies in the years that I select.  I use the formulas that you told me in the bar chart and it works well.

=Sum({$<Year,Date = {">=$(=YearStart(Today()))<=$(=MonthEnd(Today()))"}>} FactSoldAmount)

=Sum({$<Year, Date = {">=$(=YearStart(AddYears(Today(), -1)))<=$(=MonthEnd(AddYears(Today(), -1)))"}, BranchID = p({<Year = {"$(=Max(Year))"}>})>} FactSoldAmount)

=Sum({$<Year, Date = {">=$(=YearStart(AddYears(Today(), -2)))<=$(=MonthEnd(AddYears(Today(), -2)))"}, BranchID = p({<Year = {"$(=Max(Year))"}>})>}FactSoldAmount)


In the climber bar I have company in dimension and it shows me all companies. But I want to show only the companies in these years.  And when I click years it works well. Can you help me ?

sunny_talwar

Climber bar? I am not sure I know what that is