Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
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 🙂
Do data have a Date field?
I do have a date filed named CalDate. 🙂
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.
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 🙂