Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I would like to change the following expression for usage in the script:
Sum({<Sales_Date ={">=$(=Date(Today()-365)) <=$(=Date(Today()-14))"}>} Sales))
What is the best way to go about with this?
Something like this: Sum( If(Sales_Date =>Today()-365 AND Sales_Date <=Today()-14, Sales))
LOAD
IF(( Sales_Date >=(today()-365)) and (Sales_Date <= (today()-14)), Sales, 0) as Sales
...
😝Oops an "identical" answer 30 min later, sorry about that. I blame it on my multitasking, I sometime pause my response writing for doing something else and when I come back a bit later to finish up.
In the meanwhile good folks like you @Gysbert_Wassenaar comes around and brings the help faster.
Thanks, this helped me along the way. Only the sum() is giving me problems in the script.
Take a look at my previous suggested solution. It differs from @Gysbert_Wassenaar , his expression is requireing an group by in your load my is not.
Thanks.
To add to my original question a bit. I'm looking to incorporate a cumulative average in the script.
My table looks in a very basic way like:
Customer | Article | Date | Sales_date | Shipmentreceived_date | QtySold | QtyReceived |
A | A | 01-01-20 | 01-01-20 | 5 | ||
A | A | 05-01-20 | 05-01-20 | 2 | ||
A | A | 10-01-20 | 10-01-20 | 1 | ||
B | A | 01-01-20 | 01-01-20 | 5 | ||
B | A | 04-01-20 | 04-01-20 | 3 | ||
A | B | 01-01-20 | 01-01-20 | 10 | ||
B | B | 02-01-20 | 02-01-20 | 15 |
I'm trying to calculate the average stock level per date. The result should be something like this:
Customer | Article | Date | Sales_date | Shipmentreceived_date | QtySold | QtyReceived | QtySold_Cumul | QtyReceived_Cumul | Qty_stock_on_date |
A | A | 01-01-20 | 01-01-20 | 5 | 5 | 5 | |||
A | A | 05-01-20 | 05-01-20 | 2 | 2 | 5 | 3 | ||
A | A | 10-01-20 | 10-01-20 | 1 | 3 | 5 | 2 | ||
A | A | 16-01-20 | 16-01-20 | 3 | 3 | 8 | 5 | ||
A | A | 18-01-20 | 18-01-20 | 4 | 7 | 8 | 1 | ||
B | A | 01-01-20 | 01-01-20 | 3 | 3 | 3 | |||
B | A | 04-01-20 | 04-01-20 | 2 | 2 | 3 | 1 | ||
A | B | 01-01-20 | 01-01-20 | 10 | 10 | 10 | |||
B | B | 02-01-20 | 02-01-20 | 15 | 15 | 15 |
So I would like to add rows for cumulative sales per customer per article and cumulative shipments received per customer per article. These should be subtracted from each other defined by a time period (per date, or maybe week(date)). So I can get a stock average per week. So if I check the stock for 11-01-20 for customer A and article A it will show a stock level of 3.
Best I have is the following Design Blog post, this may be a way to go at it:
https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130
If you wish to search further in this area, use the following link to get to the base page that contains search box:
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
There are over 700 posts from our internal experts in this area and most are how-to, so I think this should give you some useful ideas on how to go at things for this use case. This one seems a bit complex too, so it might be worthwhile working with the services organization to have a consultant or one of our partners get up with you for a more in-depth review of things to see what what would make the best approach given the use case. Sorry I do not have anything better for you.
Regards,
Brett