Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sogloqlik
Creator II
Creator II

Number of Months as a Dim

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

 

6 Replies
rbartley
Specialist II
Specialist II

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


 

 

sogloqlik
Creator II
Creator II
Author

Thx for your answer.

 

If life were that simpleSmiley Happy

 

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...

rbartley
Specialist II
Specialist II

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. 

VariableRecordFilter.JPGThen 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).

 

 

rbartley
Specialist II
Specialist II

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.

 

VariableRecordFilter.JPG

 

 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

rbartley
Specialist II
Specialist II

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.

sogloqlik
Creator II
Creator II
Author

Thx for the input.

I will check it.