11 Replies Latest reply: Jun 19, 2017 7:04 AM by Mikel De RSS

    End of year quantity

    Mikel De

      Hi all!

       

      I have a combo chart with Date.autoCalendar.Year as a dimension. The line should represent the stock at the end of each year, i.e. the stock in December. I try to use the following formulas but they do not work:

       

      Sum({<[Date.autoCalendar.Month]={"=Max([Date.autoCalendar.Month])"}>}[Quantity]): returns the total for each year

      Sum({<[Date.autoCalendar.Month]={'12'}>}[Quantity]): returns zero

       

      Can somebody please help with this?


      It would also be nice if the line shows a value only when there is data for the entire year. Currently I have Jan 2010 until Mar 2017, so I don't want to see value for 2017 as the year has not finished yet. But this is not a vital requirement.


      Thank you!

        • Re: End of year quantity
          Manish Kachhia

          Provide sample data or sample app to work.

            • Re: End of year quantity
              Mikel De

              Please find below a very basic sample from Jan 2014 to Aug 2016.

               

               

              Data:

              Load

                Supplier,

                Country,

                Code,

                Date(Date,'MM.DD.YYYY') AS Date,

                Product,

                Quantity

              Inline

              [

              Supplier,Country,Code,Date,Product,Quantity

              Riffpath,China,CN,05.10.2015,Indigo,3283

              Janyx,Indonesia,ID,12.10.2015,Green,9381

              Dabfeed,Norway,NO,06.06.2015,Purple,6804

              Eare,Uganda,UG,24.07.2014,Maroon,782

              Wikido,China,CN,06.02.2015,Puce,5394

              Avamba,Canada,CA,18.01.2016,Red,3366

              Jaxnation,Indonesia,ID,13.08.2016,Red,5267

              Demivee,France,FR,12.08.2014,Goldenrod,4648

              Skalith,China,CN,26.04.2016,Fuscia,3665

              Skiba,France,FR,07.01.2016,Maroon,1360

              Blogtag,Peru,PE,28.01.2016,Indigo,9157

              Tagcat,China,CN,21.03.2016,Red,7388

              Edgeclub,Vietnam,VN,13.05.2016,Green,8259

              Topicware,China,CN,16.07.2014,Red,8745

              Avamm,Japan,JP,29.10.2015,Aquamarine,945

              Gabtype,Japan,JP,16.07.2015,Pink,9253

              Browsezoom,France,FR,03.09.2014,Goldenrod,8904

              Roodel,Brazil,BR,03.02.2014,Red,2147

              Divape,Russia,RU,24.07.2015,Turquoise,3359

              Vinte,Chad,TD,08.06.2014,Purple,4077

              Skajo,Indonesia,ID,25.03.2016,Red,2980

              Aivee,Botswana,BW,10.01.2015,Maroon,3067

              Talane,Indonesia,ID,25.01.2014,Fuscia,6853

              Livepath,Brazil,BR,12.06.2015,Aquamarine,9073

              Skynoodle,Indonesia,ID,16.06.2014,Pink,6683

              Meeveo,France,FR,09.01.2016,Purple,978

              Topicstorm,Sri Lanka,LK,04.10.2015,Crimson,4258

              Dazzlesphere,Philippines,PH,07.05.2016,Crimson,6205

              Feednation,Portugal,PT,17.01.2016,Fuscia,5487

              Talane,Indonesia,ID,29.12.2014,Fuscia,2410

              Devpulse,China,CN,28.10.2014,Aquamarine,3784

              Yodo,Indonesia,ID,12.06.2016,Orange,3523

              Lazz,China,CN,21.05.2015,Violet,1511

              Zoovu,Kazakhstan,KZ,10.06.2016,Indigo,8453

              Aimbu,China,CN,11.10.2015,Yellow,6255

              Riffpath,Canada,CA,26.04.2016,Puce,9417

              BlogXS,United States,US,23.09.2015,Purple,9661

              Youspan,Croatia,HR,28.06.2014,Puce,1432

              Ainyx,Croatia,HR,27.08.2014,Pink,1508

              Kaymbo,Portugal,PT,18.09.2014,Red,5507

              Wordify,Russia,RU,18.02.2015,Puce,1133

              Aimbu,Portugal,PT,18.11.2014,Indigo,1464

              Skimia,Macedonia,MK,23.07.2014,Red,2666

              Brainverse,Ukraine,UA,19.02.2015,Teal,9639

              Thoughtstorm,Indonesia,ID,23.05.2016,Khaki,9307

              Browsebug,Sweden,SE,20.07.2014,Goldenrod,2765

              Browsedrive,Peru,PE,02.03.2016,Aquamarine,4013

              Izio,China,CN,14.06.2014,Crimson,4087

              Shufflester,Iraq,IQ,09.05.2015,Khaki,2947

              Youspan,Philippines,PH,18.01.2016,Blue,935

              Flashspan,China,CN,12.01.2015,Violet,984

              Edgepulse,Malaysia,MY,08.08.2015,Maroon,9944

              Riffpedia,Indonesia,ID,27.07.2016,Crimson,4998

              Vipe,Finland,FI,15.02.2016,Goldenrod,6668

              Zoonder,China,CN,14.12.2015,Purple,3875

              Avavee,Thailand,TH,16.10.2015,Teal,4464

              Youopia,Indonesia,ID,04.11.2015,Purple,9983

              Yodoo,China,CN,25.07.2014,Indigo,2777

              Devbug,China,CN,22.12.2015,Maroon,4281

              Ntags,Thailand,TH,18.09.2014,Crimson,8677

              Feedbug,Mongolia,MN,30.07.2016,Teal,7313

              Quimm,Portugal,PT,07.10.2015,Aquamarine,3943

              Jamia,Iran,IR,22.03.2014,Crimson,2714

              LiveZ,Indonesia,ID,14.07.2015,Orange,8598

              Riffwire,Sudan,SD,26.12.2015,Maroon,8147

              Eare,Indonesia,ID,29.06.2014,Orange,7389

              Fanoodle,Ukraine,UA,16.06.2016,Indigo,3950

              Flashset,Indonesia,ID,22.07.2015,Fuscia,8660

              Voonix,Indonesia,ID,14.07.2016,Puce,6572

              Jabbersphere,New Zealand,NZ,06.07.2014,Puce,2120

              Riffwire,Philippines,PH,29.09.2015,Puce,5207

              Agivu,Sweden,SE,08.04.2014,Goldenrod,6631

              Skyble,Pakistan,PK,30.06.2014,Teal,8262

              Tagfeed,Thailand,TH,28.03.2015,Puce,9931

              Twitterbridge,China,CN,22.01.2015,Pink,9208

              Realcube,France,FR,07.10.2015,Indigo,794

              Linkbuzz,Germany,DE,05.11.2014,Yellow,7249

              Mudo,Brazil,BR,22.01.2015,Maroon,6829

              Yombu,Belarus,BY,18.01.2014,Goldenrod,2859

              Wikizz,Brazil,BR,09.02.2014,Blue,3366

              Voonyx,Malaysia,MY,12.02.2015,Mauv,9882

              Vitz,Wallis and Futuna,WF,10.04.2016,Orange,8181

              Youfeed,Philippines,PH,18.08.2016,Fuscia,6917

              Quimba,China,CN,06.04.2016,Red,5406

              Vimbo,Philippines,PH,03.06.2016,Mauv,2355

              Tazzy,Belarus,BY,14.12.2015,Violet,7169

              Yakijo,Brazil,BR,10.05.2014,Indigo,819

              Quatz,Slovenia,SI,13.08.2015,Puce,5063

              Innotype,Philippines,PH,23.03.2014,Teal,5605]

              (Delimiter is ',');

               

               

              [autoCalendar]:

                DECLARE FIELD DEFINITION Tagged ('$date')

              FIELDS

                Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

                Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

                Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

                Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

                Month($1) AS [Month] Tagged ('$month', '$cyclic'),

                Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

                Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

                Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

                Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

                Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),

                If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

                Year(Today())-Year($1) AS [YearsAgo] ,

                If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

                4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

                Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

                If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

                12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

                Month(Today())-Month($1) AS [MonthRelNo] ,

                If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

                (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

                Week(Today())-Week($1) AS [WeekRelNo] ;

               

               

              DERIVE FIELDS FROM FIELDS Date USING [autoCalendar] ;

            • Re: End of year quantity
              Dan Sullivan

              Try this (assuming you have the data in the app by individual calendar Date and the last day of the year has total you want [a running sum is not needed])

               

              Max(AGGR(SUM(Quantity),Date))

              • Re: End of year quantity
                Thomas Le Gall

                Sum({<[Date.autoCalendar.Month]={'12'}>}[Quantity]) will not work because this field is a dual format not a numeric.


                ==> Create a listbox and add in it the field [Date.autoCalendar.Month]

                ==> See how is written your "december" month (could be "dec." or "december" or something else, depends on your geographical variables).


                Then, your measure will be Sum({<[Date.autoCalendar.Month]={'The_Month_As_It_Is_Written'}>}[Quantity])

                • Re: End of year quantity
                  Jahanzeb Hashmi

                  Try this

                  Sum({<[Date.autoCalendar.Month]={"=Max(Date([Date.autoCalendar.Month],'MM'))"}>}[Quantity])