25 Replies Latest reply: Mar 7, 2018 9:53 AM by Sunny Talwar

# 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)

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)

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

• ###### Re: Year comparison in bar chart

May be try this

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

• ###### Re: Year comparison in bar chart

It works   Thank you very much !!

I also I have one question. I want to have the same chart, but When I select year in filter  it should change by this logic: if I select 2016,  the chart should be changed by comparing the companies that were only in 2016. When I select 2017 only the companies that were in 2017 should be compared in all of these years.

Is it possible?

• ###### Re: Year comparison in bar chart

May be this

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

• ###### Re: Year comparison in bar chart

it shows only the year that I select. I want to show all the years' sum, but only the selected year's  companies

• ###### Re: Year comparison in bar chart

This isn't showing you the previous year (2017) for the companies within the selected year?

• ###### Re: Year comparison in bar chart

No

• ###### Re: Year comparison in bar chart

Make sense... try this

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

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

• ###### Re: Year comparison in bar chart

it shows the same amount of these years and they aren't the same. Also, 2018 should be shown( if it's possible )

• ###### Re: Year comparison in bar chart

Share a sample if possible

• ###### Re: Year comparison in bar chart

I cant

• ###### Re: Year comparison in bar chart

Mock up data my friend...

• ###### Re: Year comparison in bar chart

It's okay.

Can I use this formula in bar chart like this?

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)

• ###### Re: Year comparison in bar chart

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

• ###### Re: Year comparison in bar chart

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!!

• ###### Re: Year comparison in bar chart

In this one?

=Sum({\$<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)

• ###### Re: Year comparison in bar chart

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

• ###### Re: Year comparison in bar chart

Replace 1 with <Year, Month>

• ###### Re: Year comparison in bar chart

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)

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 ?

• ###### Re: Year comparison in bar chart

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

• ###### Re: Year comparison in bar chart

It is an extension.  Ok, I will try by other way. And in these formulas we compare the period, how can we compare the years regarding the max(Date) information ? how the period could be changed by the max(Date) ??

I try to change the period(in all measures) by this {"=max(date)"}.  Will it work properly ?

• ###### Re: Year comparison in bar chart

I guess replace all Today() with Max(date) and it should work....

• ###### Re: Year comparison in bar chart

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

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

Thank you very much, it works. but now I have another problem. it shows only the year and last year that I select. I want to show all the year

• ###### Re: Year comparison in bar chart

All years? I have no idea what you mean... you will have to assume that I don't know anything...

• ###### Re: Year comparison in bar chart

Sorry, I don't explain well and sorry to bothering you. I mean When I select 2018 it shows me 2016,2017 and 2018 years' compare in the bar chart. When I select 2017 it shows me only 2016 and 2017 (I want to show 2018too) I had the same problem up in this discussion and you helped me that's why I asked. Sorry again and thanks a lot for your help in everything in these days

• ###### Re: Year comparison in bar chart

Oh, that might be because we have coded to look back in time....

1) expression is for max year

2) expression is for max year -1

3) expression for for max year - 2

when you select 2017, it will show 2017, 2016 and 2015 (assuming you have data for all these three years)