Qlik Community

App Development

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

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
GrizzlyBestia
Contributor II
Contributor II

How to make a KPI Show last months value

Spoiler
Hi everyone,

I am currently trying to make a KPI that shows the average Paid Weight (kg) for a selected month and the previous month.

However my formula is not calculating a value in my set analysis it is just returning - .

I am just trying to have a current value shown over last months value. Any help would be greatly appreicated. 


The below image is the value for September 2021, I want to show the value for August 2021 also.
GrizzlyBestia_0-1633427361790.png Avg({<[DataType]={'Actual'}>}[Cold Weight])

The Value for August 2021 can be seen below:

GrizzlyBestia_1-1633427445182.png  GrizzlyBestia_2-1633427465210.png



None of my solutions seem to provide an answer they simple return - , see below for my several attempts.

Avg({<[DataType]={'Actual'},MonthMin={'$(=date(AddMonths(MonthA,-Months_Back)))'}>}[Cold Weight])


Avg({<[DataType]={'Actual'},MonthMin={'$(=date(AddMonths(MonthA,-Months_Back),'MM-yyyy'))'}>}[Cold Weight])


Avg({<[DataType]={'Actual'},MonthA={'$(=date(AddMonths(MonthA,-Months_Back)))'}>}[Cold Weight])


Avg({<[DataType]={'Actual'},MonthMin={'$(=date(AddMonths(MonthA,-Months_Back)))'},MonthMax={'$(=date(monthend(AddMonths(MonthA,-Months_Back))))'}>}[Cold Weight])


Avg({<[DataType]={'Actual'},MonthMin={'$(=date(AddMonths(MonthA,-Months_Back)))'},MonthMax={'$(=date(monthend(MonthMin)))'}>}[Cold Weight])


Avg({<[DataType]={'Actual'},MonthMin={'$(=date(AddMonths(MonthA,-Months_Back)))'}>}[Cold Weight])

 



 



1 Solution

Accepted Solutions
GrizzlyBestia
Contributor II
Contributor II
Author

Hi everyone,

I have found a solution for this issue so I thought I'd share it.

I created an inline table to hold number of months a user wishes to look back. This selection drives how far the user wishes to look back in number of months:

MonthBack:
LOAD * INLINE [
Months_Back
0,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12
];

In the load editor I created a Min month and Max month variable in my calendar which I will use for this function.

MonthName(date(CalDate)) as [MonthA],//[Cal Month]
MonthName(date(CalDate)) as [MonthMin], // -> this is to show the start of the month
MonthName(date(CalDate)) as [MonthMax], // -> this is to show the end of the month

This is loaded into my App in the filterPane as a dropdown list called Months_Back, the default value is set to 0, so that the user will always see the same value at first. Once the user selects 1 it will show 1 month ago's results.

To get the comparison to work for the second KPI I had to tell the function to IGNORE the initial date selection MonthA using: , MonthA=   after declaring the field. This allowed Qlik to create another date selection.

 

Now that the initial month selection is being ignored you are free to start the set analysis.

By keeping an eye on the debugging bar at the bottom of the page allows a user to see clearly what is being analysed. You can see below that the formula states:

If MonthMin >= 01/08/2021 <=31/08/2021 then get the Cold Weight.

GrizzlyBestia_0-1633434078111.png

To calculate the MonthMax field: =date(monthend(AddMonths(MonthA,-Months_Back)))) provides the last day of the month.

The full formula can be seen below:

Avg({<[DataType]={'Actual'}, MonthA=, MonthMin={">=$(=date(AddMonths(MonthA,-Months_Back))) <=$(=date(monthend(AddMonths(MonthA,-Months_Back))))"}>}[Cold Weight])

GrizzlyBestia_1-1633434486351.png

 

GrizzlyBestia_2-1633434494179.pngThis is showing Septembers figure against Augusts.

 

I hope this helps somebody 🙂 

 

View solution in original post

4 Replies
abhijitnalekar
Specialist
Specialist

Do data have a Date field?

Regards,
Abhijit
keep Qliking...
GrizzlyBestia
Contributor II
Contributor II
Author

I do have a date filed named CalDate. 🙂

abhijitnalekar
Specialist
Specialist

Hi @GrizzlyBestia 

Please use below expressions 

Current Month : Sum({$ < CalDate={"$(='>=' & Date(MonthStart(Max(CalDate)), 'MM/DD/YYYY') & '<=' & Date((Max(CalDate)), 'MM/DD/YYYY'))"}>} [#TransactionCount])

Last Month : Sum({$ <CalDate={"$(='>=' & Date(MonthStart(Max(CalDate),-1), 'MM/DD/YYYY') & '<=' & Date(AddMonths(Max(CalDate),-1), 'MM/DD/YYYY'))"}>} [#TransactionCount])

 

Hope this will help you.

Regards,
Abhijit
keep Qliking...
GrizzlyBestia
Contributor II
Contributor II
Author

Hi everyone,

I have found a solution for this issue so I thought I'd share it.

I created an inline table to hold number of months a user wishes to look back. This selection drives how far the user wishes to look back in number of months:

MonthBack:
LOAD * INLINE [
Months_Back
0,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12
];

In the load editor I created a Min month and Max month variable in my calendar which I will use for this function.

MonthName(date(CalDate)) as [MonthA],//[Cal Month]
MonthName(date(CalDate)) as [MonthMin], // -> this is to show the start of the month
MonthName(date(CalDate)) as [MonthMax], // -> this is to show the end of the month

This is loaded into my App in the filterPane as a dropdown list called Months_Back, the default value is set to 0, so that the user will always see the same value at first. Once the user selects 1 it will show 1 month ago's results.

To get the comparison to work for the second KPI I had to tell the function to IGNORE the initial date selection MonthA using: , MonthA=   after declaring the field. This allowed Qlik to create another date selection.

 

Now that the initial month selection is being ignored you are free to start the set analysis.

By keeping an eye on the debugging bar at the bottom of the page allows a user to see clearly what is being analysed. You can see below that the formula states:

If MonthMin >= 01/08/2021 <=31/08/2021 then get the Cold Weight.

GrizzlyBestia_0-1633434078111.png

To calculate the MonthMax field: =date(monthend(AddMonths(MonthA,-Months_Back)))) provides the last day of the month.

The full formula can be seen below:

Avg({<[DataType]={'Actual'}, MonthA=, MonthMin={">=$(=date(AddMonths(MonthA,-Months_Back))) <=$(=date(monthend(AddMonths(MonthA,-Months_Back))))"}>}[Cold Weight])

GrizzlyBestia_1-1633434486351.png

 

GrizzlyBestia_2-1633434494179.pngThis is showing Septembers figure against Augusts.

 

I hope this helps somebody 🙂 

 

View solution in original post