Set Analysis to Show Un Associated Records In Qlik Sense

[h_prakash]

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.

Please help in this regard.

Thanks

Hari

[sunny_talwar]

May be this

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

[rubenmarin]

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.

[#4]

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

[jagan · Accepted solution]

Hi Hari,

Try like this

ProjectDetails:

LOAD * INLINE [

    Project, Actual Year, Dummy

    1, 2015, 0

    2, 2016, 0

    3, 2017, 0

];

AmountDetails:

LOAD * INLINE [

    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.

[jagan]

You can also try

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

[h_prakash]

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

[jagan]

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.

[rubenmarin]

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?

[antoniotiman]

Hi,

Maybe this in Script

[Project Details]:
LOAD * Inline [
Project, Standard Year
1, 2015
2, 2016
3, 2017];
Join LOAD * Inline [
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