- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to freeze sales of a particular month
Hi,
I've below sales information for difference dates.
Sales | Date |
1000 | 20-05-2022 |
2000 | 21-05-2022 |
3000 | 01-06-2022 |
So at the end of May the sales value will be 3000. I want this value to be freeze for that particular month. It means if we received another sales values as '4000' in month of June with date as 22/05/2022 I don't want value to be changed from 3000 to 7000, it has to remain as 3000. Looking for set analysis expression.
Regards,
Barat Vikas
- Subscribe by Topic:
-
Application Development
-
Client Managed
-
Creating Analytics
-
Layout & Visualizations
-
Other
-
SaaS
-
Scripting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think you'll need an extra field that determines in which month the sales belong. Right now, there's no way to separate the values if the Date field states that it's may. So maybe something like:
Sales | Date | Month_added |
1000 | 20-05-2022 | May-22 |
2000 | 21-05-2022 | May-22 |
3000 | 01-06-2022 | June-22 |
4000 | 22-05-2022 | June-22 |
I'm not sure if this is any help for you, but I don't see any way you could use set expression to select the desired values without an extra field. This way you could do a set expression like this:
sum( {$< Month_added={May-22}>} Sales)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@vikasshana do u get any other column like received date or as of date in ur data set ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@sidhiq91 No other dates in my dataset apart from one data column.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=if(sum(sales)>=3000,3000,sum(sales))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I took 3000 as an example value, we don't know how much the sales going to be.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
edit script: Date(Date#(date,'DD-MM-YYYY'),'DD-MM-YYYY') as dates
formula: =sum({1<dates={">=$(=Date(Date#('01-05-2022','DD-MM-YYYY'),'DD-MM-YYYY'))<=$(=Date(Date#('31-05-2022','DD-MM-YYYY'),'DD-MM-YYYY'))"}>}SALES)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Vikas,
Before providing the solution want to know, how you got to know that the new sales added for previous months?
Also, If it don't want to show sales in that month then in which month you want to show that Sales value?
Solution: You need another date column which help to get you know that value added in which month. If that column is not present in your current dataset, then you have change you incremental load logic, you have to add date column which will capture the load date.
Regards,
Prashant Sangle
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂