topic Re: track of purchasing quantity in QlikView

track of purchasing quantity

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have the following table sales

ItemCode Date type Quantity

AAAAA 01/03/2015 Purchase 50

AAAAA 09/03/2015 Sales 10

AAAAA 15/03/2015 Sales 20

AAAAA 15/04/2016 Sales 2

when Type = purchase , it's the first purchase of the item and when type=sales it's an action of sales of the item

how to have an expression in table in order to have a track of quantity fo the first 30 days after the first purchase ?

what I try but it's wrong How to correct it

=sum({<Date= {"$(='>=' & Date') & '<=' & Date+ 30'))"}>}Quantity)

Re: track of purchasing quantity

sunny_talwar — Thu, 02 Jun 2016 10:17:03 GMT

May be this:

=Sum({<Date= {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>} Quantity)

Re: track of purchasing quantity

sunny_talwar — Thu, 02 Jun 2016 10:19:54 GMT

Or this:

=RangeSum(Sum({<Date= {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}, type = {'Purchase'}>} Quantity), -Sum({<Date= {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}, type = {'Sales'}>} Quantity))

Re: track of purchasing quantity

MK_QSL — Thu, 02 Jun 2016 10:21:18 GMT

Something like below?

Data:

Load * Inline

[

ItemCode, Date, type, Quantity

AAAAA, 01/03/2015, Purchase, 50

AAAAA, 09/03/2015, Sales, 10

AAAAA, 15/03/2015, Sales, 20

AAAAA, 25/04/2015, Sales, 20

BBBBB, 01/03/2015, Purchase, 100

BBBBB, 12/03/2015, Sales, 10

BBBBB, 24/03/2015, Sales, 25

BBBBB, 25/03/2015, Sales, 30

BBBBB, 25/04/2015, Sales, 10

];

Left Join (Data)

Load ItemCode, FirstSortedValue(Date, Date) as MinPurDate Resident Data

Where type = 'Purchase'

Group By ItemCode;

Left Join (Data)

Load

ItemCode,

Date,

type,

Quantity,

MinPurDate,

If(Date-MinPurDate <= 30, 1, 0) as Flag

Resident Data

Where type = 'Sales';

Now use below expression

=SUM({<Flag = {1}>}Quantity)

Re: track of purchasing quantity

Anonymous — Thu, 02 Jun 2016 10:24:02 GMT

sometimes max(date)='01/09/2016' the expression will be incorrect

Re: track of purchasing quantity

sunny_talwar — Thu, 02 Jun 2016 10:25:07 GMT

Not sure I understand? Why would it be incorrect?

Re: track of purchasing quantity

Anonymous — Thu, 02 Jun 2016 10:27:35 GMT

I update my question , I would like to get the first 30 days of sales . In the example I will get 2 which is wrong

Re: track of purchasing quantity

sunny_talwar — Thu, 02 Jun 2016 10:30:47 GMT

What would be expected output then, if not 2?

Re: track of purchasing quantity

sunny_talwar — Thu, 02 Jun 2016 10:32:04 GMT

I got it, try this:

=Sum({<Date= {"$(='>=' & Date(Max({<type = {'Purchase'}>}Date), 'DateFieldFormatHere') & '<=' & Date(Max({<type = {'Purchase'}>}Date) + 30, 'DateFieldFormatHere'))"}, type = {'Sales'}>}>} Quantity)

UPDATE: This should give you a date range of 01/03/2016 - 30/03/2016

Re: track of purchasing quantity

sunny_talwar — Thu, 02 Jun 2016 10:39:21 GMT

I think this is a much better way to do it, because as Manish pointed out, the Purchase date may vary for each ItemCode and set analysis will be static. Creating a flag in the script will make your life so much more easier