Set analysis

[MindaugasBacius]

Hello

I have data like (attached):

Type	Category	Date	ID	Data
Planned	Work	2015.01.01	1	3
Fact	Work	2015.01.01	1	4
Planned	Work	2015.01.02	2	5
Fact	Sick	2015.01.02	2	6

I need to get a pivot table in front end like:

ID	Sum of planned	Sum of fact
1	3	4

The solution need to take only days where Planned and Fact categories are both Work.

Thank you in advance

[Anil_Babu_Samineni]

May be this?

Expr1

If(Category=Category,Sum({<Type = {Planned}>}Data))

Expr2

If(Category=Category,Sum({<Type = {Fact}>}Data))

[sibusiso90]

Use the following expressions and use ID as your Dimension

sum({<Type={'P'},Category={'Work'}>}Data)

sum({<Type={'F'},Category={'Work'}>}Data)

[kamal_sanguri]

Create a Pivot chart with

Dimension

ID

Expression

Sum({<Type = {'Planned'}, Category = {'Work'}>}Data) -- > This would be Sum of Planned

Sum({<Type = {'Fact'}, Category = {'Work'}>}Data) -- > This would be Sum of Fact

[vinieme12]

try below

sum({<ID = e({<Category={'Sick'}>}ID)>}Data)

app attached

[sunny_talwar · Accepted solution]

May be like attached

Updated:

Script

Table:

LOAD *,

  AutoNumber(Date&ID) as NewField;

LOAD * INLINE [

    Type, Category, Date, ID, Data

    Planned, Work, 2015.01.01, 1, 3

    Fact, Work, 2015.01.01, 1, 4

    Planned, Work, 2015.01.02, 2, 5

    Fact, Sick, 2015.01.02, 2, 6

];

Dimension

ID

Expressions

=Sum({<NewField = {"=Only({<Type = {'Planned', 'Fact'}>} Category) = 'Work'"}, Type = {'Planned'}>}Data)

=Sum({<NewField = {"=Only({<Type = {'Planned', 'Fact'}>} Category) = 'Work'"}, Type = {'Fact'}>}Data)

or

Dimension

ID

Type

Expression

=Sum({<NewField = {"=Only({<Type = {'Planned', 'Fact'}>} Category) = 'Work'"}, Type = {'Planned', 'Fact'}>}Data)

[kamal_sanguri]

Hi stalwar1‌, I really liked the way you achieved it. But still need your help to understand it further.

The Firt Part of modifier - NewField = {"=Only({<Type = {'Planned', 'Fact'}>} Category) = 'Work'"}

How it will work,

This expression would return either -1 or 0 (True or False).

{"=Only({<Type = {'Planned', 'Fact'}>} Category) = 'Work'"}

And, NewField would be any number but greater 0 (So can never be 0 or -1).

[kamal_sanguri]

Ohk.. I think it is working in a different way.. It is picking those records where for NewField  this condition

{"=Only({<Type = {'Planned', 'Fact'}>} Category) = 'Work'"} is true. Is this correct ?

[sunny_talwar]

Yup, you are right