Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
torraroger
Contributor III
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

DateUserWorkbook Elapsed Time 
May MarkPlanning Sheet2.4
MayLilyPlanning Sheet2.3
JunejohnPlanning Sheet2.1
JulyLilyPlanning Sheet3
August MarkPlanning Sheet4
AugustLilyPlanning Sheet5

 

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

DateAverage Elapsed Time
May2.35
June2.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!!!

 

1 Solution

Accepted Solutions
Kushal_Chawda

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)

View solution in original post

5 Replies
Kushal_Chawda

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

torraroger
Contributor III
Contributor III
Author

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

 

Untitled.jpg

 

 

 
Kushal_Chawda

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?

torraroger
Contributor III
Contributor III
Author

I'm thinking about two options. 

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 

Kushal_Chawda

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)

View solution in original post