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: 
hardleers
Contributor
Contributor

How to change set analysis for usage in load script

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?

 

Labels (3)
7 Replies
Gysbert_Wassenaar

Something like this: Sum( If(Sales_Date =>Today()-365 AND Sales_Date <=Today()-14, Sales))


talk is cheap, supply exceeds demand
Vegar
MVP
MVP

LOAD
IF(( Sales_Date >=(today()-365)) and (Sales_Date <= (today()-14)), Sales, 0) as Sales

... 

Vegar
MVP
MVP

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

hardleers
Contributor
Contributor
Author

Thanks, this helped me along the way. Only the sum() is giving me problems in the script. 

Vegar
MVP
MVP

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. 

hardleers
Contributor
Contributor
Author

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:

CustomerArticleDateSales_dateShipmentreceived_dateQtySoldQtyReceived
AA01-01-20 01-01-20 5
AA05-01-2005-01-20 2 
AA10-01-2010-01-20 1 
BA01-01-20 01-01-20 5
BA04-01-2004-01-20 3 
AB01-01-20 01-01-20 10
BB02-01-20 02-01-20 15

 

I'm trying to calculate the average stock level per date. The result should be something like this:

CustomerArticleDateSales_dateShipmentreceived_dateQtySoldQtyReceivedQtySold_CumulQtyReceived_CumulQty_stock_on_date
AA01-01-20 01-01-20 5 55
AA05-01-2005-01-20 2 253
AA10-01-2010-01-20 1 352
AA16-01-20 16-01-20 3385
AA18-01-2018-01-20 4 781
BA01-01-20 01-01-20 3 33
BA04-01-2004-01-20 2 231
AB01-01-20 01-01-20 10 1010
BB02-01-20 02-01-20 15 1515

 

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. 

 

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.