# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## Set Analysis to Show Un Associated Records In Qlik Sense

Hi I have a Requirement  in the Qlik Sense

I have two tables

Project Details:

 Project Standar Year 1 2015 2 2016 3 2017

Amount Details:

 Project Amount Year Amount 1 2016 10 1 2017 20 1 2018 30 2 2016 40

I have a  Amount Year Filter where user will select as 2017

He wants a  table to like this

 Project Amount 1 20 3 0

He Wants 3 Project as Standard Year equals to Amount year with Amount as 0.

Thanks

Hari

Tags (1)
1 Solution

Accepted Solutions
MVP

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

Hi Hari,

Try like this

ProjectDetails:

Project, Actual Year, Dummy

1, 2015, 0

2, 2016, 0

3, 2017, 0

];

AmountDetails:

Project, Fiscal Year, Amount

1, 2016, 10

1, 2017, 20

1, 2018, 30

2, 2016, 40

];

=Sum({<[Fiscal Year]={\$(=Max([Fiscal Year]))}>} Amount) + Sum({<[Fiscal Year]=, [Actual Year]={\$(=Max([Fiscal Year]))}>} Dummy)

Regards,

Jagan.

11 Replies
MVP

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

May be this

Sum({<[Amount Year] = p([Standard Year])>}Amount)

MVP

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

Hi Hari, maybe with:

Sum(Amount)+Sum({1<Project=p({1<[Standar Year]=P([Amount Year])>} Project)>} 0)

And uncheking 'supresss zero-values' in presentation tab.

Not applicable

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

May be table concatenation will work with out complex SET Analysis expression.

MVP

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

Hi Hari,

Try like this

ProjectDetails:

Project, Actual Year, Dummy

1, 2015, 0

2, 2016, 0

3, 2017, 0

];

AmountDetails:

Project, Fiscal Year, Amount

1, 2016, 10

1, 2017, 20

1, 2018, 30

2, 2016, 40

];

=Sum({<[Fiscal Year]={\$(=Max([Fiscal Year]))}>} Amount) + Sum({<[Fiscal Year]=, [Actual Year]={\$(=Max([Fiscal Year]))}>} Dummy)

Regards,

Jagan.

MVP

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

You can also try

=Sum({<[Fiscal Year]={\$(=Max([Fiscal Year]))}>} Amount) + Sum({<[Fiscal Year]=, [Actual Year]={\$(=Max([Fiscal Year]))}>} 0)

Contributor

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

Thank You for the response. But Still Facing the issue. I am getting Null in the Amount.

MVP

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

Hi Hari,

You can also try P()

=Sum({<[Fiscal Year]={\$(=Max([Fiscal Year]))}>} Amount) + Sum({<[Fiscal Year]=, [Actual Year]=P([Fiscal Year])>} 0)

Regards,

Jagan.

MVP

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

Hi Hari, have you checked the example attached? It works there so, what's the diffeence between the data in that sample and your document?

Honored Contributor III

## Re: Set Analysis to Show Un Associated Records In Qlik Sense

Hi,

Maybe this in Script

[Project Details]:
Project, Standard Year
1, 2015
2, 2016
3, 2017]
;
Project, Amount Year, Amount
1, 2016, 10
1, 2017, 20
1, 2018, 30
2, 2016, 40]
;
LOAD Project,If(Len(Trim([Amount Year]))=0,[Standard Year],[Amount Year]) as [Amount Year],
If(Len(Trim([Amount Year]))=0,0,Amount) as Amount
Resident [Project Details];
Drop Table
[Project Details];

Regards,

Antonio