topic Set Analysis with 2 selections based on positive or negative value in QlikView

Set Analysis with 2 selections based on positive or negative value

Tue, 29 Mar 2011 09:21:32 GMT

Hi,

I have problem with expression creation based on positive/negative values.

This is example data:

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/5857.Data_5F00_Example.xls:550:0]

Result should be like this:
Article age based on selected date: 15.07.2000 (from Master Calendar)

Article Age:
Article | 0-4 months | 4-8 months
-------------------------------------------------------------------------
X | 200 | 500
Y | 0 | 1000
Z | 500 | 0
-------------------------------------------------------------------------

Expressions should be created for "1-3 months" and "3-8 months" in a way that it sums all positive values depending on defined timeframe ("3-8 months" for example) and deduct all negative numbers earlier then Selected Date, all based on ID entry. If multiple IDs fall into same timeframe, they are summed.

My idea was to use Set Analysis system with 2 selections for same column, based on positive or negative values.

Tnx for ideas 🙂

Set Analysis with 2 selections based on positive or negative value

stephencredmond — Tue, 29 Mar 2011 10:00:36 GMT

Hi,

Use a calculated dimension for your date ranges:

=if(Date<vDate, Replace(Class((year(vDate)*12 + Month(vDate)) - (year(Date)*12 + Month(Date)) , 4), '<= x <', 'to') & ' months')

(This excludes dates greater than your selected date = vDate)

Positive values, use RangeMax:

Sum(RangeMax(0, QTY))

Negs, use RangeMin - though you might want this set if you choose to not exclude the forward dates in the calculated dimension:

Sum({<Date={"<$(=Date(vDate))"}>} RangeMin(0, QTY))

Regards,

Stephen

Set Analysis with 2 selections based on positive or negative value

Tue, 29 Mar 2011 11:45:20 GMT

I had an error in explanation for what is needed .... instead "1-3 months" it should be 0-4 and for 3-8 it should be 4-8 (like in table).

Tnx for tips, now i'm trying to implement this in my table. It is not so simple as my original table is defined with many periods ( 0-1, 1-2, 2-3, 3-6, 6-12, 12-24, 24-36 and 36+ months). It is used for calculation of quantites (and later on values) per article based on FIFO method, hence different IDs for same Article (warehouse entry tracking).

Set Analysis with 2 selections based on positive or negative value

stephencredmond — Tue, 29 Mar 2011 15:53:01 GMT

You can get the ranges you want with a nested if like this:

If(((year(vDate)*12 + Month(vDate)) - (year(Date)*12 + Month(Date))) < 0, Dual('Future', '0'),
if(((year(vDate)*12 + Month(vDate)) - (year(Date)*12 + Month(Date))) < 2, Dual('0-1', 1),
If(((year(vDate)*12 + Month(vDate)) - (year(Date)*12 + Month(Date))) < 4, Dual('2-3', 3),
If(((year(vDate)*12 + Month(vDate)) - (year(Date)*12 + Month(Date))) < 7, Dual('4-6', 6),
If(((year(vDate)*12 + Month(vDate)) - (year(Date)*12 + Month(Date))) < 13, Dual('7-12', 12),
If(((year(vDate)*12 + Month(vDate)) - (year(Date)*12 + Month(Date))) < 25, Dual('13-24', 24),
If(((year(vDate)*12 + Month(vDate)) - (year(Date)*12 + Month(Date))) < 37, Dual('25-36', 25),
Dual('36+', 36))))))))

Stephen