topic Re: Exclude field selection using set analysis not working in QlikView

Exclude field selection using set analysis not working

Jesh19 — Sun, 22 Oct 2017 07:24:55 GMT

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

Re: Exclude field selection using set analysis not working

Anil_Babu_Samineni — Sun, 22 Oct 2017 09:12:12 GMT

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

Re: Exclude field selection using set analysis not working

Jesh19 — Sun, 22 Oct 2017 09:18:08 GMT

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

Re: Exclude field selection using set analysis not working

its_anandrjs — Sun, 22 Oct 2017 09:39:00 GMT

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

Re: Exclude field selection using set analysis not working

swuehl — Sun, 22 Oct 2017 09:40:59 GMT

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)

)

Re: Exclude field selection using set analysis not working

Jesh19 — Sun, 22 Oct 2017 10:00:54 GMT

Hi Stefan,

Date formatting is needed.

Regards,

Jeshwanth

Re: Exclude field selection using set analysis not working

swuehl — Sun, 22 Oct 2017 10:09:05 GMT

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

Re: Exclude field selection using set analysis not working

Jesh19 — Sun, 22 Oct 2017 10:23:42 GMT

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

Re: Exclude field selection using set analysis not working

swuehl — Sun, 22 Oct 2017 10:28:09 GMT

Is your field maybe named FISCAL QTR instead QTR?

Could you post a small sample QVW too look at?

Re: Exclude field selection using set analysis not working

Jesh19 — Sun, 22 Oct 2017 10:30:12 GMT

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

Regards,

Jeshwanth

Re: Exclude field selection using set analysis not working

Anil_Babu_Samineni — Sun, 22 Oct 2017 10:32:39 GMT

Could you post test application. With only() function should work..

Re: Exclude field selection using set analysis not working

Jesh19 — Sun, 22 Oct 2017 10:45:33 GMT

Stefan,

It's working without date formatting.

Thanks.