Get different dates with different id's

[miguelbraga]

Good morning dear experts,

It's been a long time I'm posting a new thread on the community, but I'm with a complicated problem and need help from you experts:

Gysbert Wassenaar, Sunny Talwar, Stefan Wühl, Robert Mika, Henric Cronström, Robert Mika and Rob Wunderlich.

I'll explain my issue here:

I have a table with the following fields:

    [Lines] which specify the type of data that I'm evaluating;

    [Client Id] which have specific number of each c;

    [Date] which specify a Date where the record have been created;

    [Amount] which specify a Amount of Sales made in the Date.

There are 16 types of Lines and each one have it's own specific rule which you can see from the following picture:

The rules of each line is as follows:

• For Lines [L1, L4, L5, L8, L9, L10, L15, L16]  there is a simple Sum(Amount) expression

My problem is to calculate for Lines [L2, L3, L6, L7, L11, L12, L13 and L14] with the following rules

• For Line [L2] I need to sum the Amount of the dates that only appears in Lines L2 and not in Lines L3
  • Note: there are dates that are the same from Lines L2 and Lines L3
• For Line [L3] I need to sum the Amount of the dates that only appears in Lines L3 and not in Lines L2

• For Line [L6] I need to sum the Amount of the dates that only appears in Lines L6 and not in Lines L7
  • Note: there are dates that are the same from Lines L6 and Lines L7
• For Line [L7] I need to sum the Amount of the dates that only appears in Lines L7 and not in Lines L6

• For Line [L11] I need to sum the Amount of the dates that only appears in Lines L11 and not in Lines L12
  • Note: there are dates that are the same from Lines L11 and Lines L12
• For Line [L12] I need to sum the Amount of the dates that only appears in Lines L12 and not in Lines L11

• For Line [L13] I need to sum the Amount of the dates that only appears in Lines L13 and not in Lines L14
  • Note: there are dates that are the same from Lines L13 and Lines L14
• For Line [L14] I need to sum the Amount of the dates that only appears in Lines L14 and not in Lines L13

How can I achieve this in a expression (via a Table chart) and/or in the script (excluding the dates for the Lines [L2, L3, L6, L7, L11, L12, L13 and L14])? I will provide a example of Lines L2 and L3 so if you could help me with that rule I can do the same for the rules of the pairs [L6,L7], [L11, L12] and [L13,L14]. Can you help me?

Best regards and a great weekend,

Data Architect MB

[vinieme12]

try as below

LOAD Lines,

    [Client Id],

    Date,

    Lines&'_'&NUM(Date) as Ln_dt_Key,

    Amount

FROM

(biff, embedded labels, table is Sheet1$);

Expression

=Pick(PurgeChar(Lines,'L'),

0,                                                                           //expression L1 if any!

Sum({<Ln_dt_Key={'L2*'}>}Amount)                         //expression for L2

,    

Sum({<Ln_dt_Key={'L3*'}>}Amount)                         //expression for L3

)

[antoniotiman]

Hi Miguel,

try this

Sum({<Date=P({<Lines={'L2'}>})-P({<Lines={'L3'}>})>} Amount)

Regards,

Antonio

[PrashantSangle]

Hi,

try with indirect set analysis

For Line L2

sum({<Date=e({<Lines={"L3"}>}Date)>}Amount)

For L3 Sales

sum({<Date=e({<Lines={"L2"}>}Date)>}Amount)

Regards,

Prashant Sangle

[sam_grounds · Accepted solution]

Hi Miguel,

If I may, I'll throw my two cents in. I'm sure such a knowledgeable group of people could put together a better solution, but here is how I would tackle it. I haven't tested it and wrote it outside QlikView, So I'll apologise if it errors.

=pick(match(Lines,'L1','L2','L3','L4','L5','L6','L7','L8','L9','L10','L11','L12','L13','L14','L15','L16'),

sum(Amount),

sum(total{$<Date=P({$<Lines={'L2'}>})>-$<Date=P({$<Lines={'L3'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L3'}>})>-$<Date=P({$<Lines={'L2'}>})>}Amount),

sum(Amount),

sum(Amount),

sum(total{$<Date=P({$<Lines={'L6'}>})>-$<Date=P({$<Lines={'L7'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L7'}>})>-$<Date=P({$<Lines={'L6'}>})>}Amount),

sum(Amount),

sum(Amount),

sum(Amount),

sum(total{$<Date=P({$<Lines={'L11'}>})>-$<Date=P({$<Lines={'L12'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L12'}>})>-$<Date=P({$<Lines={'L11'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L13'}>})>-$<Date=P({$<Lines={'L14'}>})>}Amount),

sum(total{$<Date=P({$<Lines={'L14'}>})>-$<Date=P({$<Lines={'L13'}>})>}Amount),

sum(Amount),

sum(Amount)

)

[sunny_talwar]

I wonder if this is across all the clients or are you talking about individual clients? The problem is that if this is for each client, then you have not provided two different line types for a single client in your data....

[miguelbraga]

Hey there experts,

I'll try any sugestions that you guys have told me. When I'm done, I'll consider the best option as the correct answer.

Many thanks,

MB

See you soon!

[effinty2112]

Hi Miguel,

I have the same question as Sunny, nevertheless try :

MapLines:

Mapping

LOAD * INLINE [

    F1, F2

    L1, L1

    L2, L2L3

    L3, L2L3

    L4, L4

    L5, L5

    L6, L6L7

    L7, L6L7

    L8, L8

    L9, L9

    L10, L10

    L11, L11L12

    L12, L11L12

    L13, L13L14

    L14, L13L14

    L15, L15

    L16, L16

];

Data:

LOAD

ApplyMap('MapLines',Lines) as MapLines,

*;

LOAD Lines,

     [Client Id],

     Date,

     Amount

FROM

Example.xls

(biff, embedded labels, table is Sheet1$);

JoinTable:

LOAD

Distinct

Date,

MapLines,

//[Client Id],

1 as Flag

Resident Data

Where Lines = MapLines;

LOAD

Date,

MapLines,

//[Client Id],

if(Count(DISTINCT Lines)=1,1,0) as Flag

Resident Data

Where Lines <> MapLines

Group by Date, MapLines

//,[Client Id]

;

Left Join(Data) LOAD * Resident JoinTable;

DROP Table JoinTable;

There are lines with the Client Id field commented out. Whether these remain commented out depends on the answer to the question posed by Sunny.

The required expression in your front end will be sum(Flag*Amount).

Cheers

Andrew

[miguelbraga]

Thank you Sam Grounds,

This solution is the best and most suitable for me and works just like a charm without the need to change the script.

Regards,

Data Architect MB

[sam_grounds]

You're very welcome! I'm glad it was helpful.