topic Re: How to get max date in QlikView

How to get max date

kunkumnaveen — Thu, 16 Nov 2017 11:09:36 GMT

Hello All,

In a line chart I have two Dimensions:

1)Fiscal Year

2)PROD_HIER4_Name

I need to write expression where it has to pick MAX--VALIDFROM date's ---->Average PRICE_MAT at PROD_HIER4_Name level

in the above case it has to be (2208.27+1722.9)/2 irrespective of BMATERIAL

I Tired the below expression but I am not getting any output...

aggr(avg({<VALIDFROM={'$(=max(PRICE_MAT))'}>}PRICE_MAT),PROD_HIER4_Name)

what am I missing in above expression

Thanks

Re: How to get max date

shraddha_g — Thu, 16 Nov 2017 11:25:39 GMT

VALIDFROM ={"$(=max(VALIDFROM))"}

Re: How to get max date

kunkumnaveen — Thu, 16 Nov 2017 12:35:36 GMT

Hi, Could u say what am I missing in second chart

Chart1) avg({<[FISCAL YEAR]=>}PRICE_MAT)

Chart 2) avg({<VALIDFROM ={"$(=max(VALIDFROM))"},[FISCAL YEAR]= >}PRICE_MAT)

In second chart I am getting only selected fiscal year ,even though I disable the fiscal year selection

what am I missing?

Re: How to get max date

shraddha_g — Thu, 16 Nov 2017 12:38:31 GMT

It will only give you data for max validfrom date.

What is the max(VALIDFROM) returnig?

Re: How to get max date

sunny_talwar — Thu, 16 Nov 2017 12:39:21 GMT

Because your ValidFrom is a date and it might only show up in the most recent year...

Re: How to get max date

shraddha_g — Thu, 16 Nov 2017 12:42:31 GMT

What is the dimension you have used in chart?

Re: How to get max date

kunkumnaveen — Thu, 16 Nov 2017 12:44:45 GMT

For Each fiscal year I need to show Max Valid form's Average price

Re: How to get max date

kunkumnaveen — Thu, 16 Nov 2017 12:46:02 GMT

Dimension:

1)Fiscal Year

2)PROD_HIER4_Name

Re: How to get max date

sunny_talwar — Thu, 16 Nov 2017 12:50:40 GMT

Then may be try this

Avg({<[FISCAL YEAR]>} Aggr(If(Only({<[FISCAL YEAR]>} VALIDFROM) = Max({<[FISCAL YEAR]>} TOTAL <[FISCAL YEAR]> VALIDFROM), Only({<[FISCAL YEAR]>} PRICE_MAT)), [FISCAL YEAR], VALIDFROM))

Re: How to get max date

kunkumnaveen — Thu, 16 Nov 2017 12:59:14 GMT

I Am not getting any value for the above expression...

Can u help me , what should I need to do in the below expression to display all the FY data irrespective of selection

avg({<VALIDFROM ={"$(=max(VALIDFROM))"},[FISCAL YEAR]= >}PRICE_MAT)

Re: How to get max date

shraddha_g — Thu, 16 Nov 2017 13:01:14 GMT

Use expression given by sunny as it is

Re: How to get max date

sunny_talwar — Thu, 16 Nov 2017 13:02:36 GMT

Set analysis is evaluated once per chart. In your case you are looking for Max(VALIDFROM) per FISCAL YEAR. Set analysis won't be able to do this and you will need to use Aggr() function

Re: How to get max date

kunkumnaveen — Thu, 16 Nov 2017 13:02:54 GMT

I Am not getting any value for the above expression...

I did but no out put

Re: How to get max date

sunny_talwar — Thu, 16 Nov 2017 13:04:16 GMT

How about this

Avg({<[FISCAL YEAR]>} Aggr(If(Only({<[FISCAL YEAR]>} VALIDFROM) = Max({<[FISCAL YEAR]>} TOTAL <[FISCAL YEAR]> VALIDFROM), Sum({<[FISCAL YEAR]>} PRICE_MAT)), [FISCAL YEAR], VALIDFROM))

Re: How to get max date

kunkumnaveen — Thu, 16 Nov 2017 13:46:14 GMT

Sunny ,I almost achieve my requirement ,only thing I had change is instead of SUM I used AVG ....

one issues even though my chart has two dimension

Dimension:

1)Fiscal Year

2)PROD_HIER4_Name

if I don't selected any value from PROD_HIER4_Name then it is picking up that fiscal years max date price irrespective of PROD_HIER4_Name,

if I selected any values from PROD_HIER4_Name(Lets say Arjun) then I am getting Arjun Avg values per Fy

so how can I make same thing to display even before selection ,

I tried like this .....

Avg({<[FISCAL YEAR]>} Aggr(If(Only({<[FISCAL YEAR]>} VALIDFROM) = Max({<[FISCAL YEAR]>} TOTAL <[FISCAL YEAR]> VALIDFROM), avg({<[FISCAL YEAR]>} PRICE_MAT)),[FISCAL YEAR],VALIDFROM,PROD_HIER4_Name))

but no use....I am getting same

Before Selecting Arjun

After Selecting Arjun

Re: How to get max date

sunny_talwar — Thu, 16 Nov 2017 14:03:22 GMT

May be this

Avg({<[FISCAL YEAR]>} Aggr(If(Only({<[FISCAL YEAR]>} VALIDFROM) = Max({<[FISCAL YEAR]>} TOTAL <[FISCAL YEAR], PROD_HIER4_Name> VALIDFROM), Avg({<[FISCAL YEAR]>} PRICE_MAT)),[FISCAL YEAR],VALIDFROM,PROD_HIER4_Name))