topic Re: Set analysis in QlikView

Set analysis

MindaugasBacius — Mon, 22 May 2017 12:18:46 GMT

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

Re: Set analysis

Anil_Babu_Samineni — Mon, 22 May 2017 12:27:27 GMT

May be this?

Expr1

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

Expr2

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

Re: Set analysis

sibusiso90 — Mon, 22 May 2017 12:28:55 GMT

Use the following expressions and use ID as your Dimension

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

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

Re: Set analysis

kamal_sanguri — Mon, 22 May 2017 12:29:54 GMT

Create a Pivot chart with

Dimension

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

Re: Set analysis

vinieme12 — Mon, 22 May 2017 12:30:10 GMT

try below

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

app attached

Re: Set analysis

sunny_talwar — Mon, 22 May 2017 12:30:18 GMT

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

Expressions

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

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

Dimension

Type

Expression

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

Re: Set analysis

kamal_sanguri — Tue, 23 May 2017 06:08:02 GMT

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

Re: Set analysis

kamal_sanguri — Tue, 23 May 2017 06:17:55 GMT

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 ?

Re: Set analysis

sunny_talwar — Tue, 23 May 2017 08:41:32 GMT

Yup, you are right