topic Re: How to show last year as one column and year_month for current year on same plot in QlikView

How to show last year as one column and year_month for current year on same plot

AntSto — Tue, 29 Mar 2022 09:09:04 GMT

Hello,

I'd like to show the average results of last year sales as 1 column, then the average results of last year's last trimster sales on a second column and then have one column per month to show the current year results.

Can I do that on only 1 plot? Best I could get so far was to have year_month columns for current year and use reference lines to show the averages of last year and last year's last trimester which isn't exactly my need.

What I'd like:

and what I get:

Thanks for your time!

EDIT: I managed to get last year showing up as year and year_month for 2022 with an expression like this:

=If( [Date_col.autoCalendar.Year] LIKE '2022', [Date_col.autoCalendar.YearMonth],[Date_col.autoCalendar.Year])

So this is good but I'm unsure if I could add the last year's last trimester on the same axis too (or maybe I need to double the lines for last trimester with a new col that will act as a condition check for x-axis?)

Re: How to show last year as one column and year_month for current year on same plot

dmac1971 — Mon, 28 Mar 2022 09:36:11 GMT

You could do this will a rolling calc maybe? Dimension will be whatever needed but not your date/month field. Then an expression to show -15 to -3 months from current month, then another to do -6 to -3 months from current month, then current month -2, then -1 and then current month, this way you wont need to change it again in the future?

Re: How to show last year as one column and year_month for current year on same plot

AntSto — Tue, 29 Mar 2022 08:32:29 GMT

thank you for the suggestion but I'd like to have the last year and last year's trimester be fixed, even when I'll be in october and not have a rolling calc if possible.

But I think you pointed something intersting I could maybe calculate which month I'm in and from that get the last year. I'll try and post it here if I can manage something working!

Re: How to show last year as one column and year_month for current year on same plot

AntSto — Tue, 29 Mar 2022 10:00:24 GMT

I managed to do it by loading the table twice and adding one extra column(second_loading) that I set to 0 in one table and to 1 in the other.

Then for my axis I use this:

```

=If(
[Date_col.autoCalendar.Year] LIKE '2022',
[Date_col.autoCalendar.YearMonth],
if([Date_col.autoCalendar.YearQuarter] LIKE '2021-Q4' and [second_loading] = 0,
[Date_col.autoCalendar.YearQuarter],
[Date_col.autoCalendar.Year]))

```

Then I need to make sure values are only counted once with distinct: