topic Re: Number of Months as a Dim in App Development

Number of Months as a Dim

sogloqlik — Mon, 14 Jan 2019 09:42:05 GMT

Hi.

I have an Application with sales data. I want to reduce the data dynamically which means I want to filter out customer according to the number of months since their last purchase:

My initial approach was like this: for each customer I know this last date, I can even calculate the number of month between that last date and today and put it as an attribute of a customer. Now, how can I put it as a Dim? For example if I want to show the measures only for the customers who bought in the last 6 months? Or 5?

Or is there another approach?

Thx.

Motty

Re: Number of Months as a Dim

rbartley — Mon, 14 Jan 2019 09:56:03 GMT

Hi Motty,

If you only want to show customers with sales in the last x months, then couldn't you just add a condition in the load script to the customer table, e.g. first load the table that includes the attribute showing whether the customer has sales in the last x months then create another table, referencing the first as a Resident with a condition based on this attribute and then drop the first table?

Pseudo code:

[All Customers]:
LOAD
Customer ID,
Customer Name,
..
Show_Customer_Flag
from
....


[Recent Customers]:
LOAD
Customer ID,
Customer Name,
...
Resident [All Customers]
Where Show_Customer_Flag = 1;

Drop table [All Customers];

Re: Number of Months as a Dim

sogloqlik — Mon, 14 Jan 2019 10:12:07 GMT

Thx for your answer.

If life were that simple

Of course I could, But my users want to change the X months dynamically. e.g. if they choose 6 months I need to show all the customers who have pouched in the last 6 months(which means 6 is the max number of months) but then they may want to choose 5 months or even a whole year...

Re: Number of Months as a Dim

rbartley — Mon, 14 Jan 2019 12:16:33 GMT

Well, there are two ways as far as I can tell:

1) Keep the number of months in an excel spreadsheet (or similar) and load this in your app. Each time the user changes the number of months, they would have to re-load the app. This is obviously very clunky.

2) Create a variable in the load script, e.g. let vMonths = 6 and use the Variable Input component (included as part of the Qlik Sense November 2018 release, but I believe it is available on QlikBranch if you're using a previous version) so that the user can change the variable value, e.g.

Then you can use the variable in the dimension or measure formula to filter the results, e.g.

dimension

=if(MonthsSinceOrder<=$(vMonths),[Customer Name])

measure

if(MonthsSinceOrder<=$(vMonths), Sum([Sheet1-1.Sales]))

Or using set analysis: Sum({<MonthsSinceOrder={"<=$(=vMonths)"}>}[Sheet1-1.Sales])

The records shown change as you change the variable value in the input box.

I've attached an app demonstrating this (you'll need to unzip it).

Re: Number of Months as a Dim

rbartley — Mon, 14 Jan 2019 13:47:17 GMT

I'm not sure what happened here. I posted an update but it doesn't seem to have been added. Anyway here goes:

I think the most flexible solution would be to use the Variable Input component that comes as standard with the November 2018 version of Qlik Sense (and I believe you can download from QlikBranch is using an earlier version.

First, create a variable to hold the user input value indicating the maximum number of months since the customer purchased, e.g.

Let vMonth = 6;

Now add the Variable Input component to your sheet and change the definition of your Customer list and Sales measure, e.g.

List definition: =if(MonthsSinceOrder<=$(vMonths),[Customer Name])

Sales definition: Sum({<MonthsSinceOrder={"<=$(=vMonths)"}>}[Sheet1-1.Sales])

Both the list and the table including the sales changes with the value entered in the Variable Input box.

I have attached an example app (you will need to unzip) that shows this in action. Just change the number in the input box to see the effect.

Regards,

Richard

Re: Number of Months as a Dim

rbartley — Mon, 14 Jan 2019 13:53:01 GMT

I found this on QlikBranch in case you don't have QS November 2018:

https://developer.qlik.com/garden/5ac921580f9f4d1cc1e0c168

I haven't tried it though.

Re: Number of Months as a Dim

sogloqlik — Mon, 14 Jan 2019 15:12:34 GMT

Thx for the input.

I will check it.