Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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])
Provide sample data or sample app to work.
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))
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])
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] ;
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])
a listbox is a filterpane
You need to know how is exactly written "december" in the field [Date.autoCalendar.Month]
"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
Thanks, but the last day does not represent the total.
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!