Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for
Did you mean:
Highlighted
Contributor III

## Trend expression for monthly measure

Hi all,

I don't know much about QSense expressions language and I need the expression to get the trend (positive or negative) of a monthly measure.

The data is the following (records of what users are consulting and the response time)

Table: Query Detail

 Date User Workbook Elapsed Time May Mark Planning Sheet 2.4 May Lily Planning Sheet 2.3 June john Planning Sheet 2.1 July Lily Planning Sheet 3 August Mark Planning Sheet 4 August Lily Planning Sheet 5

With this table, I would get that the Planning Sheet Workbook has the following Average Elapsed Time per month:

 Date Average Elapsed Time May 2.35 June 2.1 July 3 August 4.5

So, I want to build an expression that tells me if the tendence is increasing or decreasing. In this case is increasing.

I don't know if I need something like the first month-last month and see if there's a difference, or also taking into account the weight of usage...

Thanks!!!

Labels (6)

• ### Tutorial

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Trend expression for monthly measure

I am answering for difference between first and last month

first create two fields like below in calendar

month(Date) as Month,
num(month(Date)) as MonthNum,

then create calculated dimension

=aggr(if((sum({<MonthNum={"\$(=max(MonthNum))"}>}Sales)-sum({<MonthNum={"\$(=min(MonthNum))"}>}Sales))>0,'Positive','Negative'),Text)

5 Replies
Highlighted
MVP

## Re: Trend expression for monthly measure

What exactly you want to achieve? Can you please elaborate and provide exact output you need?

Highlighted
Contributor III

## Re: Trend expression for monthly measure

I now have a pivot table (See the table attached)

In this table I have the average elapsed time of the different Workbooks for every month. (Dimensions Workbook and Month, Measure Average Elapsed Time). The data is gathered from the example Query Detail table I posted.

I would like to get the following:

Adding a column in this table with only two outputs available for each Workbook:

1- POSITIVE: When the trend over the month is decreasing, negative

2- NEGATIVE: When the trend over the month is increasing, positive

Highlighted
MVP

## Re: Trend expression for monthly measure

How would you decide increasing or decreasing? Comparing current month with previous month?  If current month Avg is more than previous month Avg then increasing?

Highlighted
Contributor III

## Re: Trend expression for monthly measure

Comparing the Average Elapsed Time between the first month (january) and the actual,

Or, the one would be best:

Comparing the same but with the weight of Count(workbook) to give more importance to the months that have been more used.

Thanks

Highlighted
MVP

## Re: Trend expression for monthly measure

I am answering for difference between first and last month

first create two fields like below in calendar

month(Date) as Month,
num(month(Date)) as MonthNum,

then create calculated dimension

=aggr(if((sum({<MonthNum={"\$(=max(MonthNum))"}>}Sales)-sum({<MonthNum={"\$(=min(MonthNum))"}>}Sales))>0,'Positive','Negative'),Text)