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!

[MK_QSL]

Provide sample data or sample app to work.

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

[thomaslg_wq · Accepted solution]

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

[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] ;

[mikel_de]

Hi,

Sorry, but I don't know what is a listbox.

Tried the following but it returns a flat line: Sum({<[Date.autoCalendar.Month]={'December'}>}[Quantity])

[thomaslg_wq]

a listbox is a filterpane

You need to know how is exactly written "december" in the field [Date.autoCalendar.Month]

[thomaslg_wq]

"december" for example is not the same as "December" take care about upper case because qlik is case_sensitive

And depending on your country and computer language it may not be in english

[mikel_de]

Thanks, but the last day does not represent the total.

[mikel_de]

In the Main script it is exactly as I used it - "December". Now I do not have my computer in front of me but maybe I should try with the short month name. Will let you know when I do. Thanks for the help!