Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Re: Current and previous year

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Zwax

Contributor III

2024-04-29
09:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Current and previous year

Hi,

So, I need some help. I’m probably missing something, but I cannot figure out what. I have searched for a solution without success.

I need to show current month and same month last year next to each other.

Calculation is:

sum([amount]) / sum([value]) * 100

Current year can easily be solved without set analysis like this:

if([Year] = max([Year]),sum([amount]) / sum([value]) * 100,)

This will work with and without selections.

Now the issue with adding previous year. First I just used the "above" function, which worked…. until I made a selection in the frontend. I know I will need to use set analysis somehow.

I have tried the following:

Aggr({1< [Year] = {"$(=Max([Year]-1 ))"}>} sum([amount]) / sum([value]) * 100, [Year_month])

This returns zero.

If I remove the set analysis part and just write: “Aggr sum([amount]) / sum([value]) * 100, [Year_month])” it works – but naturally this will not give me previous year.

Any suggestions on how to add the set analysis par (or other ways of solving it)?

1 Solution

Accepted Solutions

marcus_sommer

MVP

2024-04-29
11:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Essentially is that aggregated measures belong to **their** dimension-values and couldn't be not assigned to another one. Means in your case you couldn't show aggregations for the previous year against the current year. At least not with the standard-usage.

Of course there are various ways to get such views. One might be just to show all dimensions-values side by side and then using interrecord-functions like the above() to address any cell-reference within the chart. With more or less complexity in regard to aggr() wrappings you may consider any selections and/or excluded values.

Another way would be to add dummy-values in the script to your dimension which are not directly associated with the normal data, like:

concatenate(calendar) load 'dummy' as Year autogenerate 1;

and then you could query it in the chart, like:

if(Year = 'dummy',

sum({< Year = {"$(=max(Year))"}>} Value) / sum({< Year = {"$(=max(Year)-1)"}>} Value),

sum(Value))

Also possible would be to add an extra or artificial dimension within a pivot and then querying the dimensionality() to branch into different calculations like above with the dummy-query.

Especially by just a few columns it would be much easier to remove the dimensionality and replacing it with expressions, in your case maybe just these 3 ones:

sum({< Year = {"$(=max(Year))"}>} Value)

sum({< Year = {"$(=max(Year)-1)"}>} Value)

sum({< Year = {"$(=max(Year))"}>} Value) / sum({< Year = {"$(=max(Year)-1)"}>} Value)

133 Views

2 Replies

marcus_sommer

MVP

2024-04-29
11:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Essentially is that aggregated measures belong to **their** dimension-values and couldn't be not assigned to another one. Means in your case you couldn't show aggregations for the previous year against the current year. At least not with the standard-usage.

Of course there are various ways to get such views. One might be just to show all dimensions-values side by side and then using interrecord-functions like the above() to address any cell-reference within the chart. With more or less complexity in regard to aggr() wrappings you may consider any selections and/or excluded values.

Another way would be to add dummy-values in the script to your dimension which are not directly associated with the normal data, like:

concatenate(calendar) load 'dummy' as Year autogenerate 1;

and then you could query it in the chart, like:

if(Year = 'dummy',

sum({< Year = {"$(=max(Year))"}>} Value) / sum({< Year = {"$(=max(Year)-1)"}>} Value),

sum(Value))

Also possible would be to add an extra or artificial dimension within a pivot and then querying the dimensionality() to branch into different calculations like above with the dummy-query.

Especially by just a few columns it would be much easier to remove the dimensionality and replacing it with expressions, in your case maybe just these 3 ones:

sum({< Year = {"$(=max(Year))"}>} Value)

sum({< Year = {"$(=max(Year)-1)"}>} Value)

sum({< Year = {"$(=max(Year))"}>} Value) / sum({< Year = {"$(=max(Year)-1)"}>} Value)

134 Views

Zwax

Contributor III

2024-05-15
06:45 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Ok. The above answer made me aware that it's not as straight forward as I have hoped.

For now I have chosen a quick fix to show what I need (created a new table on year/month in the loadscript - calculated the value and joined it by itself to get prev year). Probably need to look into this some more.

Thanks for the reply 🙂

69 Views

Community Browser