How to make a KPI Show last months value

[GrizzlyBestia]

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.
 Avg({<[DataType]={'Actual'}>}[Cold Weight])

The Value for August 2021 can be seen below:

  

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

[abhijitnalekar]

Do data have a Date field?

[GrizzlyBestia]

I do have a date filed named CalDate. 🙂

[abhijitnalekar]

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.

[GrizzlyBestia · Accepted solution]

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.

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

This is showing Septembers figure against Augusts.

I hope this helps somebody 🙂