Selection pointing another field

[qlikviewaf]

Hi,

My dataset is as follow:

YEAR - MONTH - DAY

I tried to create a formula to get some data based on the selection on the fields above, that have to poin to another field.

YEAR -> YEAR_SURGERY

MONTH -> MONTH_SURGERY

DAY -> DAY_SURGERY

=Sum({<Invoice_Date=,Day_Surgery={$(=concat(chr(39) & Day & chr(39),',') )},Month_Surgery={$(=concat(chr(39) & Month & chr(39),',') )},Year_Surgery={$(=concat(chr(39) & Year & chr(39),',') )},Day=,Year=,Month=,Quarter=,Invoice_Date_Num=,>}WI_EUR)

As you can see, based on the selection done on Day/Month/Year, i would like to get data related to DAy_Surgery,Month_Surgery,Year_Surgery. 

This is working, but i have an issue:  if i select a low level data, like a specific product, the formula doesn't work. Seems like the formula to work, needs that a data on that day/month/year exist, otherwise will not extract the data on the Day_Surgery/Month_Surgery/Year_Surgery.

Any suggestion?

Thanks

[settu_periasamy]

Hi,

Not sure, but can you try to remove the Day,Month, Year? You already added into this in Set expression, but again it is getting ignore. is that correct?

=Sum({<Invoice_Date=,Day_Surgery={$(=concat(chr(39) & Day & chr(39),',') )},Month_Surgery={$(=concat(chr(39) & Month & chr(39),',') )},Year_Surgery={$(=concat(chr(39) & Year & chr(39),',') )},Day=,Year=,Month=,Quarter=,Invoice_Date_Num=,>}WI_EUR)

or try like

=Sum({<Invoice_Date=,Day_Surgery=P(Day),

Month_Surgery=P(Month),Year_Surgery=P(Year),Invoice_Date_Num>}WI_EUR)