1

I try to create a timeline chart for every year for products on the market that are started to sell before that year and ended to sell after that year.

To get my filtered data I filter them like I described above. Then I union my data from the different years.

All this works and I get the right data back. Also the grouping it self works, but when I try to use a timeline chart, it doesn't let me do it.

SAQL:

q_A = filter q by (date('start_year', 'start_month', 'start_day') in [.."3 years ago"]) && (date('end_year', 'end_month', 'end_day') in ["2 years ago"..]);
q_A = group q by 'all' full, q_A by 'all';
q_A = foreach q_A generate unique(q_A.'PlaceHolder') as 'A', toDate((date_diff("year", toDate("1969", "yyyy"), now()) - 3) * 31536000) as 'Date';

q_B = filter q by (date('start_year', 'start_month', 'start_day') in [.."2 years ago"]) && (date('end_year', 'end_month', 'end_day') in ["1 years ago"..]);
q_B = group q by 'all' full, q_B by 'all';
q_B = foreach q_B generate unique(q_B.'PlaceHolder') as 'A', toDate((date_diff("year", toDate("1969", "yyyy"), now()) - 2) * 31536000) as 'Date';

q_C = filter q by (date('start_year', 'start_month', 'start_day') in [.."1 years ago"]) && (date('end_year', 'end_month', 'end_day') in ["current year"..]);
q_C = group q by 'all' full, q_C by 'all';
q_C = foreach q_C generate unique(q_C.'PlaceHolder') as 'A', toDate((date_diff("year", toDate("1969", "yyyy"), now()) - 1) * 31536000) as 'Date';

q_D = filter q by (date('start_year', 'start_month', 'start_day') in [.."current year"]) && (date('end_year', 'end_month', 'end_day') in ["1 year ahead"..]);
q_D = group q by 'all' full, q_C by 'all';
q_D = foreach q_C generate unique(q_C.'PlaceHolder') as 'A', toDate((date_diff("year", toDate("1969", "yyyy"), now())) * 31536000) as 'Date';

result = union q_A, q_B, q_C, q_D;
q_new = group result by 'Date';
q_new = foreach q_new generate 'Date' as 'Date', sum('A') as 'Sum';

Results:

enter image description here

Every help is greatly appreciated. Thanks

0

I tried with SAQL a lot of different ways, but the only thing that worked was that I used Recipies and calculated for every row for every year, if it was on the market. Lets call them "IP_[YEAR]"

Then I ordered it by StartDate and summed every "IP_[YEAR]" up. I created a new column and summed all "IP_[YEAR]" columns to one "sum" column together. Then I hid the "IP_[YEAR]" columns and it worked.

Here the SAQL to the resolution:

q = filter q by (date('start_year', 'start_month', 'start_day') in ["2 years ago".."8 years ahead"];
result = group q by 'start_year';
result = foreach result generate q.'start_year' as 'start_year', sum(q.'IP_2019') as 'B', sum(q.'IP_2020') as 'C', sum(q.'IP_2021') as 'D', sum(q.'IP_2022') as 'E', sum(q.'IP_2023') as 'F', sum(q.'IP_2024') as 'G', sum(q.'IP_2025') as 'H', sum(q.'IP_2026') as 'I', sum(q.'IP_2027') as 'J', sum(q.'IP_2028') as 'K', sum(q.'IP_2008') as 'L', sum(q.'IP_20091') as 'M', sum(q.'IP_2010') as 'N', sum(q.'IP_2011') as 'O', sum(q.'IP_2012') as 'P', sum(q.'IP_2013') as 'Q', sum(q.'IP_2014') as 'R', sum(q.'IP_2015') as 'S', sum(q.'IP_2016') as 'T', sum(q.'IP_2017') as 'U', sum(q.'IP_2018') as 'V';
result = foreach result generate 'start_year' as 'start_year', 'B' as 'B', 'C' as 'C', 'D' as 'D', 'E' as 'E', 'F' as 'F', 'G' as 'G', 'H' as 'H', 'I' as 'I', 'J' as 'J', 'K' as 'K', 'L' as 'L', 'M' as 'M', 'N' as 'N', 'O' as 'O', 'P' as 'P', 'Q' as 'Q', 'R' as 'R', 'S' as 'S', 'T' as 'T', 'U' as 'U', 'V' as 'V', ((((((((((((((((((('B' + 'C') + 'D') + 'E') + 'F') + 'G') + 'H') + 'I') + 'J') + 'K') + 'L') + 'M') + 'N') + 'O') + 'P') + 'Q') + 'R') + 'S') + 'T') + 'U') + 'V' as 'A';
result = order result by 'start_year' asc;

Maybe it will help somebody.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.