topic Re: How to change set analysis for usage in load script in QlikView

How to change set analysis for usage in load script

hardleers — Sat, 16 Nov 2024 19:13:22 GMT

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?

Re: How to change set analysis for usage in load script

Gysbert_Wassenaar — Tue, 28 Jan 2020 09:04:54 GMT

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

Re: How to change set analysis for usage in load script

Vegar — Tue, 28 Jan 2020 09:33:20 GMT

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

...

Re: How to change set analysis for usage in load script

Vegar — Tue, 28 Jan 2020 09:38:43 GMT

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

Re: How to change set analysis for usage in load script

hardleers — Wed, 29 Jan 2020 07:48:00 GMT

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

Re: How to change set analysis for usage in load script

Vegar — Wed, 29 Jan 2020 08:21:28 GMT

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.

Re: How to change set analysis for usage in load script

hardleers — Wed, 29 Jan 2020 13:29:25 GMT

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.

Re: How to change set analysis for usage in load script

Brett_Bleess — Mon, 24 Feb 2020 14:41:09 GMT

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