Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

h_prakash
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:

  

ProjectStandar Year
12015
22016
32017

Amount Details:

   

ProjectAmount YearAmount
1201610
1201720
1201830
2201640

   

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

He wants a  table to like this

  

ProjectAmount
120
30

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

Please help in this regard.

Thanks

Hari

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

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.

11 Replies

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

May be this

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

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
MVP

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

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.

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

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

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?

antoniotiman
Honored Contributor III

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

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

Community Browser