Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
k_t_12
Contributor II
Contributor II

Creating a KPI for counts of stores who have closed

Hi all,

New to Qlik Sense. 

I have a pivot table that looks like the following with calculated columns:

Store ID Last Month with Sales: MAX(Month)

 

Reported in Previous Month?

IF( (InMonth(Max(Month), TODAY(2), -1))=0,'NO','YES')

Store Status

IF( (InMonth(Max(Month), TODAY(2), -1))=0,'Closed','Open')

Store_1 5/31/2024 NO Closed
Store_2 11/30/2024 YES Open
Store_3 11/30/2024 YES Open

 

The above table is meant to show Stores, their most recent month with sales, whether or not a store made a sale in the last month, and based off of that, whether the store is closed or not. If a store made a sale in the past month they are considered "Open". If a store has not made a sale in previous month, they are considered "Closed".

I want to create two separate KPIs that simply counts the number of stores who are considered 'Closed' and 'Open'.

Please advise on how to create an expression to count 'Closed' and 'Open' stores in a KPI chart. 

Thank you! 

Labels (2)
1 Solution

Accepted Solutions
pravinboniface
Creator III
Creator III

Assuming your data is:

 

mydata:
load * inline [
store,sale_date
Store_1,5/31/2024
Store_1,11/30/2024
Store_2,11/30/2024
Store_3,11/30/2024
Store_4,5/31/2024
];

exit Script;

 


Then your "Open" KPI will be:

count(distinct {<sale_date={">=$(=monthstart(AddMonths(today(),-1)))<=$(=monthend(AddMonths(today(),-1)))"}>}store)

And your "Closed" KPI would be:
count(distinct store) -
count(distinct {<sale_date={">=$(=monthstart(AddMonths(today(),-1)))<=$(=monthend(AddMonths(today(),-1)))"}>}store)

View solution in original post

11 Replies
pravinboniface
Creator III
Creator III

Assuming your data is:

 

mydata:
load * inline [
store,sale_date
Store_1,5/31/2024
Store_1,11/30/2024
Store_2,11/30/2024
Store_3,11/30/2024
Store_4,5/31/2024
];

exit Script;

 


Then your "Open" KPI will be:

count(distinct {<sale_date={">=$(=monthstart(AddMonths(today(),-1)))<=$(=monthend(AddMonths(today(),-1)))"}>}store)

And your "Closed" KPI would be:
count(distinct store) -
count(distinct {<sale_date={">=$(=monthstart(AddMonths(today(),-1)))<=$(=monthend(AddMonths(today(),-1)))"}>}store)

pravinboniface
Creator III
Creator III

The more elegant solution would be to add a status of 'Open' or 'Closed' in the load script.  Then it will be a simple set analysis statement in the KPI that would be very easy to maintain.

mydata:
load * inline [
store,sale_date
Store_1,5/31/2024
Store_1,11/30/2024
Store_2,11/30/2024
Store_3,11/30/2024
Store_4,5/31/2024
];

left Join
load store, if (InMonth(max(date(date#(sale_date,'MM/DD/YYYY'))),Today(2),-1)=0, 'Closed','Open') as status
resident mydata
group by store;

exit Script;
k_t_12
Contributor II
Contributor II
Author

Hi pravinboniface,

Thank you for assisting with this - unfortunately this did not return the expected counts.

The "Open" KPI returns 0, while the "Closed" KPI is returning the total number of distinct stores in my dataset. 

 

The data that I'm using is loaded from a SQL table so I'm not sure if that makes a difference. 

For some clarity here is an example of what my SQL table looks like: 

Month Store ID Store Name Sales
1/31/2024 Store_1 Red Shop 4
2/29/2024 Store_1 Red Shop 30
3/31/2024 Store_1 Red Shop 35
4/30/2024 Store_1 Red Shop (null)
5/31/2024 Store_1 Red Shop 20
6/30/2024 Store_1 Red Shop 5
2/29/2024 Store_2 Blue Store 19
3/31/2024 Store_2 Blue Store 25
4/30/2024 Store_2 Blue Store 35
5/31/2024 Store_2 Blue Store 32

 

Thank you for your help.

pravinboniface
Creator III
Creator III

It sounds like a date format issue.  Can you please check your date format and also can you ensure you are using the correct column names?

k_t_12
Contributor II
Contributor II
Author

Hi,

I believe I found the issue - I forgot that my dates appear as datetime in Qlik - so they look like the following:

 

k_t_12_1-1733855955792.png

 

I had used the LEFT function to hide the timestamp as it's irrelevant: =LEFT([Month],10)

I tried to incorporate LEFT into the expression you wrote for me above but it did not seem to work. 

 

NoahF
Contributor III
Contributor III

The date should be stored as a numeric value. Try use the floor() function before. It will get rid of the following time component in the date. You can combine date(floor(<YourField>)). 
You should already use the floor() function in the script, to simplify your expression in the table.

k_t_12
Contributor II
Contributor II
Author

Hi Noah,

Thanks for the input - I updated both expressions as follows:

Open KPI:

count(distinct {<[FLOOR(Month))]={">=$(=monthstart(AddMonths(today(),-1)))<=$(=monthend(AddMonths(today(),-1)))"}>}Store_ID)

Closed KPI:

count(distinct Store_ID) - count(distinct {<[floor((Month))]={">=$(=monthstart(AddMonths(today(),-1)))<=$(=monthend(AddMonths(today(),-1)))"}>}Store_ID)

 

the Open KPI now shows the distinct count of stores while the Closed KPI shows 0. 

NoahF
Contributor III
Contributor III

You can not use the floor() function like that in the first part of the setAnalysis.
The expression should still look like this:

 

Count(distinct {<Month = {"<=$(=monthend(AddMonths(today(), -1))) >=$(=monthstart(AddMonths(today(), -1)))"}>}[Store_ID])

 

and

 

count(distinct Store_ID) - count(distinct [Store_ID]) - Count(distinct {<Month = {"<=$(=monthend(AddMonths(today(), -1))) >=$(=monthstart(AddMonths(today(), -1)))"}>}[Store_ID])

 

Use the floor() Function prior in the script. In the data-model your Month field should have the right tags of a date field like this:

NoahF_0-1733864083482.png

 

k_t_12
Contributor II
Contributor II
Author

Hi Noah,

Thank you for the clarification and patience with this!

I'm currently trying to figure out how to incorporate the FLOOR function as my data is currently being loaded in via a SQL query, and I'm having some difficulty updating the query since my Month field is already considered a Date datatype. I will update this topic again once I can figure it out. 

When I look in Qlik Sense presently, my date field does match up with what your screenshot shows:

k_t_12_0-1733873169846.png