8 Replies Latest reply: Aug 28, 2018 10:47 AM by Arthur Montenegro RSS

    comparison between  months in my sales

    Arthur Montenegro

      Hi! I would like to make a comparison between  months in my sales, can  anyone help me with the formula? There is attached a sample of the data that I am handling .


      Thank you!

        • Re: comparison between  months in my sales
          Marina Dor

          Hi !

           

          May you be more precise about what you need ? chart, graph, do you need only months in dimension of your analysis ? Do you want to show sales, % of increase...?

          Do you have a sample app ?

           

          Regards,

          Marina

          • Re: comparison between  months in my sales
            Breno Morais Meneses Batista

            Hello Arthur,

             

            If you need simple chart, I will give for you:

            Qlik Sense Desktop.jpg

            You just need add in dimensions:

            =Month([Sales date])

            and measures:

            Sum(Value*Qtd)

             

             

            I help you? Please mark correct answer

            • Re: comparison between  months in my sales
              Derek Emlet

              Hi Arthur,

               

              Before making any charts in your app. Your going to want to create a date table for your sales. In your data sales load make sure to Floor your primary date field, like Sale Date.

              Load

              blah,

              blah,

              Date(FLOOR([Sale_Date])) AS Sale_Date,

              FROM

               

              Then in a separate Section I would pass in the Sale_Date to your Date Table. This will create new formatted fields for you to use in charts, set analysis, etc, all using your sale date.

               

              Let vStartDate=Floor(MakeDate(2016,1,1));

              Let vEndDate=Floor(Today());

              Let vDiff=vEndDate-vStartDate+1;

               

              Sale_Date:

              Qualify*;

              Unqualify Sale_Date;

              Load *,

                Date(MonthStart(Sale_Date), 'YYYY-MM') As YearMonth,

                Year & '-' & Quarter As YearQuarter,

                WeekYear & '-' & Num(Week, '00') As YearWeek;

              Load

                Sale_Date,

                Year(Sale_Date) As Year,

                Month(Sale_Date) As Month,

                Date(Sale_Date) As Date,

                Day(Sale_Date) As Day,

                Week(Sale_Date) As Week,

                'Q' & Ceil(Month(Sale_Date)/3) As Quarter,

                WeekYear(Sale_Date) As WeekYear,

                -Year2Date(Sale_Date) As YTD_Flag,

                -Year2Date(Sale_Date, -1) As LYTD_Flag,

                If( DayNumberOfQuarter(Sale_Date) <= DayNumberOfQuarter(Today()), 1, 0) as IsInQTD,

                If( Day(Sale_Date) <= Day(Today()), 1, 0) as IsInMTD,

                If( Month(Sale_Date) = Month(Today()), 1, 0) as IsCurrentMonth,

                If( Month(AddMonths(Sale_Date,1)) = Month(Today()), 1, 0) as IsLastMonth,

                If( DayNumberOfYear(Sale_Date) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD;

              Load

                RecNo()-1+$(vStartDate) As Sale_Date

              AutoGenerate($(vDiff));

               

              Furthermore, I found this Set analysis expression usefull for comparing against a previous row (Dimension) in the chart. For example, previous Month. Each Month would show a percent increase/decrease compared to previous month.

               

              Use as measure - with Month or Month-Year field as dimension. Line Charts, with Area, and Measure coloring look nice.

               

              (Sum(Sales)

              -above(Sum(Sales)))

              /

              -above(Sum(Sales)))