Nested If

[Anonymous]

I have the following QlikView model:

I need to add a calculated dimension called "Generic Drug Entry Date".  This is calculated on the basis of the following fields:

1. DrugSubstanceFlag
2. Patent Expiry Date
3. ExclusivityDate

DrugSubstanceFlag takes two values either 'Y' or 'N'.

If the DrugSubstanceFlag = 'Y' and "Patent Expiry Date" is greater than "ExclusivityDate", then "Generic Drug Entry Date" =  "Patent Expiry Date", Otherwise "Generic Drug Entry Date" = "Exclusivity Date".  However an item/product denoted by "Trade Name" can have more than two "DrugSubstanceFlag" = 'Y', in this case the higher "Patent Expiry Date" will be the "Generic Drug Entry Date"

If the DrugSubstanceFlag = 'N' , then the "Generic Drug Entry Date" is Today's date.

The resulting table should look as follows:

There should only be a single Generic Drug Entry Date for each Trade Name entry

How can i accomplish the above?

Thank you in advance.

[vishsaggi]

Did not understand what you mean by this?

"However an item/product denoted by "Trade Name" can have more than two "DrugSubstanceFlag" = 'Y', in this case the higher "Patent Expiry Date" will be the "Generic Drug Entry Date"

can you elaborate please.

However, for the other conditions you mentioned you can try like below:

     IF(DrugSubstanceFlag = 'Y' and [Patent Expiry Date] > [ExclusivityDate], [Patent Expiry Date],

        IF(DrugSubstanceFlag = 'Y' and [Patent Expiry Date] < [ExclusivityDate], [Exclusivity Date],

           IF(DrugSubstanceFlag = 'N', Today(), [Generic Drug Entry Date]))) AS GenericDrugEntryDate

[Anonymous]

Thank you very much.  I am using a calculated dimension which is as follows:

=IF(DrugSubstanceFlag = 'Y' and [Patent Expiry Date] > [ExclusivityDate] or If(DrugSubstanceFlag = ' ' and [Patent Expiry Date] > ExclusivityDate, [Patent Expiry Date]), If(DrugSubstanceFlag = ' ', Now(),ExclusivityDate))

I get the following table, which is not what i am looking for, dates in the Generic Entry Date column are not dates:

Regards

Chris

[vishsaggi]

So just use the Date() function around the expr like

Date(yourexpr, 'DD/MM/YYYY')

[Anonymous]

Thanks for your input but does not work in my case

[Anonymous]

I have used the following Calculated Dimension:

=IF(DrugSubstanceFlag = 'Y' and [Patent Expiry Date] > [ExclusivityDate] or If(DrugSubstanceFlag = ' ' and [Patent Expiry Date] > ExclusivityDate, [Patent Expiry Date]),ExclusivityDate)

With the following results

The results are almost there but wrong in the case of the circled data.  Expiration date is greater than ExclusivityDate and should therefore be the Generic Entry Date

Thanks

[vishsaggi]

‌will check tomorrow. Don’t have access to the system.