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

    Year comparison in bar chart

    K Kv

      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.

        • Re: Year comparison in bar chart
          Sunny Talwar

          May be try this

           

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

            • Re: Year comparison in bar chart
              K Kv

              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
                  Sunny Talwar

                  May be this

                   

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

                   

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

                   

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

              • Re: Year comparison in bar chart
                K Kv

                Capture.PNG No

                  • Re: Year comparison in bar chart
                    Sunny Talwar

                    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)

                     

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

                  • Re: Year comparison in bar chart
                    K Kv

                    Capture.PNG

                     

                    Capture.PNG

                     

                    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
                        Sunny Talwar

                        Share a sample if possible

                          • Re: Year comparison in bar chart
                            K Kv

                            I cant

                              • Re: Year comparison in bar chart
                                Sunny Talwar

                                Mock up data my friend...

                                  • Re: Year comparison in bar chart
                                    K Kv

                                    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)

                                      • Re: Year comparison in bar chart
                                        Sunny Talwar

                                        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
                                            K Kv

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

                                                  • Re: Year comparison in bar chart
                                                    K Kv

                                                    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
                                                        Sunny Talwar

                                                        Replace 1 with <Year, Month>

                                                          • Re: Year comparison in bar chart
                                                            K Kv

                                                            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 ?