Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Running into an issue where with DUALs having to be 1-1 with relation to a detailed age (Year Month Day). Ends up returning the first one it pulls versus the correct detailed age.
e.g. 9136 can only be represented by 1 text. It should store two values in memory that aren't tied to each other for a detailed age formatted in XX years, XX months, XX days.
Wondering if anyone has run into this before and if there is a workaround solution in Qlik Sense SaaS.
When using using Dual() the intrinsic numeric value is what will be associated not the text representation
Can you post some examples
Hi Vineeth -
Here's a screenshot of what we are getting. Non dual age is correct.
Also paste the expression used for both dual and non dual fields
DUAL
IF(birthdate < [Tournament Start Date], DUAL(age(DATE([Tournament Start Date] + [Round Number] - 1),[birthdate]) & ' Years, '
//
&(if(day(DATE([Tournament Start Date] + [Round Number] - 1)) >= day([birthdate]),
mod((Year(DATE([Tournament Start Date] + [Round Number] - 1))*12+Month(DATE([Tournament Start Date] + [Round Number] - 1)))-(Year([birthdate])*12+Month([birthdate])),12),
if(mod((Year(DATE([Tournament Start Date] + [Round Number] - 1))*12+Month(DATE([Tournament Start Date] + [Round Number] - 1)))-(Year([birthdate])*12+Month([birthdate])),12)=0,11,mod((Year(DATE([Tournament Start Date] + [Round Number] - 1))*12+Month(DATE([Tournament Start Date] + [Round Number] - 1)))-(Year([birthdate])*12+Month([birthdate])),12)-1))) &' Months, '
//Days
&(if(day(DATE([Tournament Start Date] + [Round Number] - 1)) >= day([birthdate]),
day(DATE([Tournament Start Date] + [Round Number] - 1))-day([birthdate]),
DATE([Tournament Start Date] + [Round Number] - 1)-addmonths([birthdate],12* AGE(DATE([Tournament Start Date] + [Round Number] - 1), [birthdate])+mod(Month(DATE([Tournament Start Date] + [Round Number] - 1))-Month([birthdate])-1,12))))&' Days'
// Numeric representation: (Days)
,(date(DATE([Tournament Start Date] + [Round Number] - 1)) - date([birthdate])))) as [Detailed Age at Round Start]
NON-DUAL
=IF(birthdate < [Tournament Start Date], age(DATE([Tournament Start Date] + [Round Number] - 1),[birthdate]) & ' Years, '
//
&(if(day(DATE([Tournament Start Date] + [Round Number] - 1)) >= day([birthdate]),
mod((Year(DATE([Tournament Start Date] + [Round Number] - 1))*12+Month(DATE([Tournament Start Date] + [Round Number] - 1)))-(Year([birthdate])*12+Month([birthdate])),12),
if(mod((Year(DATE([Tournament Start Date] + [Round Number] - 1))*12+Month(DATE([Tournament Start Date] + [Round Number] - 1)))-(Year([birthdate])*12+Month([birthdate])),12)=0,11,mod((Year(DATE([Tournament Start Date] + [Round Number] - 1))*12+Month(DATE([Tournament Start Date] + [Round Number] - 1)))-(Year([birthdate])*12+Month([birthdate])),12)-1))) &' Months, '
//Days
&(if(day(DATE([Tournament Start Date] + [Round Number] - 1)) >= day([birthdate]),
day(DATE([Tournament Start Date] + [Round Number] - 1))-day([birthdate]),
DATE([Tournament Start Date] + [Round Number] - 1)-addmonths([birthdate],12* AGE(DATE([Tournament Start Date] + [Round Number] - 1), [birthdate])+mod(Month(DATE([Tournament Start Date] + [Round Number] - 1))-Month([birthdate])-1,12))))&' Days')