Front end expression

[MindaugasBacius]

Hello,

I would like to ask for advice.

I do have table:

I want to sum the Sales that is Type P and Category D except if aggregation of Date and ID Type F Category is not L.

The end result should be:

Date,          ID,    Sum(Sales)         

2017.01.01, 111,    1

Thank you!

[sunny_talwar · Accepted solution]

May be this

=Sum({<Type = {'P'}, Category = {'D'}>-<Key = {"=Count(DISTINCT {<Type = {'F'}, Category = {'L'}>} Key) > 0"}>}Sale)

Where Key is created in script like this

Table:

LOAD Date&ID as Key,

  *;

LOAD Date,

     ID,

     Type,

     Category,

     Sale

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

[MindaugasBacius]

I definitely need to improve my set analysis skills.

As always you're unbeatable

[Kushal_Chawda]

I know you got the answer but just to give you script solution

Data:

LOAD Category,

     Date,

     ID,

     Sale,

     Type

FROM

[Book.xls]

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

Left Join(Data)

LOAD Distinct Date,

     ID,

     1 as Flag

Resident Data

Where (Type='F' and Category='L');

Final:

NoConcatenate

LOAD *

Resident Data

where IsNull(Flag) and Type<>'F';

DROP Table Data;