Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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)
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;
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.
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?
Hi,
I believe I found the issue - I forgot that my dates appear as datetime in Qlik - so they look like the following:
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.
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.
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.
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:
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: