Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
)
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..
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
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))
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)
)
Hi Stefan,
Date formatting is needed.
Regards,
Jeshwanth
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).
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
Is your field maybe named FISCAL QTR instead QTR?
Could you post a small sample QVW too look at?
No my field name is QTR. I've labelled it as FISCAL QTR.
Regards,
Jeshwanth