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: 
Anonymous
Not applicable

Date-Difference Related Calculation

Hi Everyone,

I would much appreciate your help with the following:

I have a table with a Date Column, CustomerID, ProductID and a Column of Production Percentage for each ProductID.

This Percentage is for the End of the Month in each line.

Here is a sample:

DateCustomerIDProductIDProduction % at End of Month
31/05/201744441070%
30/06/201744441090%
31/05/201744442030%
30/06/201744442060%
31/05/20179999100%
30/06/2017999910100%
31/05/20179999200%
30/06/20179999200%
31/07/201799992080%

So, for example, ProductID 10 for customer 4444 was at 70% at the end of May and at 90% production at the end of June.

That means that During June Production % was 20% (=90% - 70%).

Another example: ProductID 20 for customer 9999 was at 0% at the end of May and also at the end of June. This product was at 80% production at the end of July. Therefore, during July Production % for this product was 80%.

What I would like to ask is what kind of calculation should I use to create this difference of % in production for each product?

Here is how I would like the Difference to show, more or less:

CustomerIDProductIDJuneJuly
44441020%0%
44442030%0%
999910100%0%
9999200%80%

Hope my question is clear.

and thanks in advance for any response.

Sandra

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Sandra,

try like this

Temp:

LOAD MonthName(Date) as Month,

     CustomerID,

     ProductID,

     Num([Production % at End of Month]) as %Production

FROM [lib://File]

(html, codepage is 28591, embedded labels, table is @1);

NoConcatenate LOAD *,If(CustomerID=Peek(CustomerID) and ProductID=Peek(ProductID),

RangeSum(%Production,-Peek(%Production)),%Production) as Diff

Resident Temp Order By CustomerID,ProductID,Month;

Drop Table Temp;

Regards,

Antonio

View solution in original post

4 Replies
antoniotiman
Master III
Master III

Hi Sandra,

try like this

Temp:

LOAD MonthName(Date) as Month,

     CustomerID,

     ProductID,

     Num([Production % at End of Month]) as %Production

FROM [lib://File]

(html, codepage is 28591, embedded labels, table is @1);

NoConcatenate LOAD *,If(CustomerID=Peek(CustomerID) and ProductID=Peek(ProductID),

RangeSum(%Production,-Peek(%Production)),%Production) as Diff

Resident Temp Order By CustomerID,ProductID,Month;

Drop Table Temp;

Regards,

Antonio

felipedl
Partner - Specialist III
Partner - Specialist III

Hi Sandra,

Try the following

x:

Load * Inline

[

Date,CustomerID,ProductID,Production % at End of Month

31/05/2017,4444,10,70%

30/06/2017,4444,10,90%

31/05/2017,4444,20,30%

30/06/2017,4444,20,60%

31/05/2017,9999,10,0%

30/06/2017,9999,10,100%

31/05/2017,9999,20,0%

30/06/2017,9999,20,0%

31/07/2017,9999,20,80%

];

NoConcatenate

data:

Load

RowNo() as Id,

MonthName(Date) as Month,

*,

fabs(if(RowNo()=1 or ProductID<>Previous(ProductID),num([Production % at End of Month],'#0,0'),(1-[Production % at End of Month])-(1-previous([Production % at End of Month])))) as NewPercentage;

Load

Date,

CustomerID,

ProductID,

[Production % at End of Month]

Resident x

order by CustomerID,ProductID,Date;

drop table x;

The above code gives me the picture bellow.Sample.png

Felipe.

Anonymous
Not applicable
Author

Hi Antonio

Thanks a lot for your reply. It was a great help and ultimately worked well.

I had a problem, which I managed to solve somehow by now -

My Fact table was actually concatenated with other tables. and the noconcatenate clause created a problem.

What I did to solve this was to create another Load and using Resident to the the last table in the script.

Anyway Thanks a lot. Brilliant.

Sandra

Thanks for your reply as well. felipedl

antoniotiman
Master III
Master III

Sandra,

don't worry, You can remove my 'NoConcatenate' prefix.

Also, if You want 0 % value instead of - sign, then replace Expression with

RangeSum(Diff)

Regards,

Antonio