# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Creator

## End of year quantity

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

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!

1 Solution

Accepted Solutions
Highlighted
Creator III

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

11 Replies
Highlighted
MVP

Provide sample data or sample app to work.

Highlighted
Creator II

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

Highlighted
Creator III

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

Highlighted
Creator

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

Data:

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

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

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

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

Realcube,France,FR,07.10.2015,Indigo,794

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

Highlighted
Creator

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

Highlighted
Creator III

a listbox is a filterpane

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

Highlighted
Creator III

"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

Highlighted
Creator

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

Highlighted
Creator

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!