Set Analysis with 2 selections based on positive or negative value

[#1]

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 🙂

[stephencredmond]

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

[#3]

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

[stephencredmond]

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