Exclude field selection using set analysis not working

[Jesh19]

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.

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

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

[Jesh19]

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 · Accepted solution]

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]

Hi Stefan,

Date formatting is needed.

Regards,

Jeshwanth

[swuehl]

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]

This is not working.

My values are changing according to the quarter.

Before selecting the quarter filed:

After Selecting the QTR field.

You can see the values are getting changed.

Regards,

Jeshwanth

[swuehl]

Is your field maybe named FISCAL QTR instead QTR?

Could you post a small sample QVW too look at?

[Jesh19]

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

Regards,

Jeshwanth