Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
AntSto
Contributor II
Contributor II

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

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:

 

Qliksense_need.png

 and what  I get: 

Qliksense_so_far.png

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

 

Labels (2)
1 Solution

Accepted Solutions
AntSto
Contributor II
Contributor II
Author

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:

AntSto_0-1648548009334.png

 

View solution in original post

3 Replies
dmac1971
Creator III
Creator III

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?

AntSto
Contributor II
Contributor II
Author

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!

AntSto
Contributor II
Contributor II
Author

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:

AntSto_0-1648548009334.png