Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Jesh19
Creator II
Creator II

Exclude field selection using set analysis not working

Hi All,

I've created two pivot tables.

In the first table, my dimension is YEAR & QTR and i have made them as columns.

In the second table, my dimension is only YEAR.

\Below is the screenshot.

programs.png

My problem is  i don't want the values to get changed in 2nd table when i am selecting quarter field.

For No of NPL Programs - Count ({<QTR=>}Distinct PRG_ID) is working fine

But for NPL Programs < 6 months

=Count({<QTR=>}DISTINCT

If((Aggr((Year(Date(SHIPDATE,'DD/MM/YYYY'))*12)+month(Date(SHIPDATE,'DD/MM/YYYY')),PRG_ID)-Aggr((Year(Date(ORDERDATE,'DD/MM/YYYY'))*12)+month(Date(ORDERDATE,'DD/MM/YYYY')),PRG_ID))<=6, PRG_ID))

The above expression is changing upon the selection of QTR.

I want the values to be for the current fiscal year in the second table instead of changing upon selections.

Could someone please help me in solving the issue.

Thanks in Advance.

Regards,

Jeshwanth

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Have a look at

Set Analysis in the Aggr function

So maybe try (I believe you don't need the Date() formatting functions BTW):

=Count({<QTR=>}DISTINCT

If(

     (Aggr(

          (Year( Only({<QTR=>} SHIPDATE ) )*12)+month( Only({<QTR=>}SHIPDATE) )

     ,PRG_ID)

     -

     Aggr(

          (Year( Only({<QTR=>}ORDERDATE) )*12)+month( Only({<QTR=>}ORDERDATE) )

     ,PRG_ID)

)<=6, PRG_ID)

)

View solution in original post

11 Replies
Anil_Babu_Samineni

Can you look for identifiers in qlikview. Like {1} using this it will constant even filter happens. And you need to arrange degrade field within aggr too..

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Jesh19
Creator II
Creator II
Author

Hi Anil,

I've tried using {1}...even this isn't making my values constant.

And I've no idea with degrade field in AGGR. Could you please help.

Regards,

Jeshwanth

its_anandrjs

Try with this and add year also for bypass the year selection

=Count( {1} {<QTR=,Year=>}DISTINCT

If((Aggr((Year(Date(SHIPDATE,'DD/MM/YYYY'))*12)+month(Date(SHIPDATE,'DD/MM/YYYY')),PRG_ID)-      Aggr((Year(Date(ORDERDATE,'DD/MM/YYYY'))*12)+month(Date(ORDERDATE,'DD/MM/YYYY')),PRG_ID))<=6, PRG_ID))

swuehl
MVP
MVP

Have a look at

Set Analysis in the Aggr function

So maybe try (I believe you don't need the Date() formatting functions BTW):

=Count({<QTR=>}DISTINCT

If(

     (Aggr(

          (Year( Only({<QTR=>} SHIPDATE ) )*12)+month( Only({<QTR=>}SHIPDATE) )

     ,PRG_ID)

     -

     Aggr(

          (Year( Only({<QTR=>}ORDERDATE) )*12)+month( Only({<QTR=>}ORDERDATE) )

     ,PRG_ID)

)<=6, PRG_ID)

)

Jesh19
Creator II
Creator II
Author

Hi Stefan,

Date formatting is needed.

Regards,

Jeshwanth

swuehl
MVP
MVP

Well, If you think it's needed (but I can't see why if you are applying Year / Month functions afterwards), re-apply the format, if you want.

My main point is the use of set analysis in the Aggr() inner aggregation functions (Only() explicitely added where missing).

Jesh19
Creator II
Creator II
Author

This is not working.

My values are changing according to the quarter.

Before selecting the quarter filed:

qtr unselected.png

After Selecting the QTR field.

You can see the values are getting changed.

qtr selected.png

Regards,

Jeshwanth

swuehl
MVP
MVP

Is your field maybe named FISCAL QTR instead QTR?

Could you post a small sample QVW too look at?

Jesh19
Creator II
Creator II
Author

No my field name is QTR. I've labelled it as FISCAL QTR.

Regards,

Jeshwanth