Hi, I am looking to grab a hand at turning 8 product charts into one table with Sparkline's if possible for trend tracking.
I am currently using Trellis split on my dashboard to populate these 8 line charts showing the number of hits per month over the course of 12 months for which product. My data is stored on a lookup table.csv.
My date field is stored as 04/02/2022 0:00 (4th feb). ProductType has things like - Candles, Teaset, Books
I would instead prefer to show the Products in one table with a trendline/sparkline for each product tracking the last 12 months.
To get the trellis working i currently use the below. Which seems to work well and as needed with expected results.
| inputlookup XXX.csv
| search ProductType="*"
| search ProductDate="*2022*"
| eval Date=strftime(strptime(ProductDate,"%d/%m/%Y"),"%b-%y")
| chart count(ProductType) by Date, ProductType limit=0
| fields - OTHER, "-"
| eval rank=case(ProductDate like "Jan-%",1,ProductDate like "Feb-%",2,ProductDate like "Mar-%",3,ProductDate like "Apr-%",4,ProductDate like "May-%",5,ProductDate like "Jun-%",6,ProductDate like "Jul-%",7,ProductDate like "Aug-%",8,ProductDate like "Sep-%",9,ProductDate like "Oct-%",10,ProductDate like "Nov-%",11,ProductDate like "Dec-%",12,1=1,13)
| rex field=ProductDate "-(?<rank_year>\d+)"
| sort 0 rank_year, rank
| fields - rank rank_year
However, when trying to get the sparklines/trendlines working using the below two attempts i do not get the results required. All Sparklines show a value of 0 - yet there are results for these fields being purchased on all these diff dates.
i have changed the search times, tried to add buckets, spans... even eval _time over Date and not having much luck.
| inputlookup XXX.csv
| search ProductType="*"
| search ProductDate="*2022*"
| eval Date=strftime(strptime(ProductDate,"%d/%m/%Y"),"%b-%y")
| chart sparkline count(Date) by ProductType, ProductDate limit=0
| fields - OTHER, "-"
| eval rank=case(ProductDate like "Jan-%",1,ProductDate like "Feb-%",2,ProductDate like "Mar-%",3,ProductDate like "Apr-%",4,ProductDate like "May-%",5,ProductDate like "Jun-%",6,ProductDate like "Jul-%",7,ProductDate like "Aug-%",8,ProductDate like "Sep-%",9,ProductDate like "Oct-%",10,ProductDate like "Nov-%",11,ProductDate like "Dec-%",12,1=1,13)
| sort 0 rank_year, rank
| fields - rank rank_year
And
| inputlookup XXX.csv
| search ProductType="*"
| search ProductDate="*2022*"
| eval Date=strftime(strptime(ProductDate,"%d/%m/%Y"),"%d/%m/%Y")
| chart sparkline count(ProductDate) by AppType limit=0
I believe i am going wrong with the date eval but have tried a fair few combos now with nearly all same results with sparklines always showing 0.
I have a about a years worth of data i want to track in the one visual table ( Very similar to how splunk does there own EQ example. ( to many products to show nicely on a line graph).
Thanks