topic Re: Calculated Dimension in QlikView

Calculated Dimension

albertestrada — Wed, 25 Nov 2020 16:16:04 GMT

Can someone explain why a simple calculation does not work when calculating a dimension?

if (max(BILL_DATE)>Today(),'T','F')

The expression is OK, although it creates a invalid Dimension.

Thank you,

Re: Calculated Dimension

sunny_talwar — Wed, 26 Oct 2016 18:16:37 GMT

Try adding this:

If(Max(TOTAL BILL_DATE) > Today(), 'T', 'F')

Re: Calculated Dimension

sunny_talwar — Wed, 26 Oct 2016 18:17:32 GMT

or you can do this:

Aggr(If(Max(BILL_DATE) > Today(), 'T', 'F'), BILL_DATE)

Re: Calculated Dimension

albertestrada — Thu, 27 Oct 2016 13:52:40 GMT

Thank you Sunny both options worked! Does this mean only aggregated type formulas work in the dimension?

I have a more complex example where I am generating a recency score by customer. The formula works below in a measure within a table by customer. Although when I try to use the recency score as a dimension it does not work.

See sample app attached.

Variable
# of Days since last transaction	Max Date of Data Set	minus	Max Date per Customer
vLastTransaction	MAX(total{<MONTHDIFF = {">=0<=11"}, BILL_TYPE_DESC = {"Invoice"} >}BILL_DATE)	-	MAX({<MONTHDIFF = {">=0<=11"}, BILL_TYPE_DESC = {"Invoice"} >} BILL_DATE)
Formula to assign Recency Score
Formula	Comment
if [$(vLastTransaction)] < 8 ,3,	//Score 3 : if last transaction less than 1 week
if [$(vLastTransaction)] >= 8 and [$(vLastTransaction)] < 15 ,2,	//Score 2 : if last transaction within 2 weeks
if [$(vLastTransaction)] >= 15 and [$(vLastTransaction)] < 22 ,1,	//Score 1 : if last transaction within 3 weeks
if [$(vLastTransaction)] >= 22 and [$(vLastTransaction)] < 29 ,0,	//Score 0 : if last transaction within 4 weeks
if [$(vLastTransaction)] >= 29 and [$(vLastTransaction)] < 60, -1,	//Score -1 : if last transaction within 2 months
if [$(vLastTransaction)] >= 60 and [$(vLastTransaction)] < 90 ,-2,	//Score -2 : if last transaction within 3 months
if [$(vLastTransaction)] >= 90 ,-3,'N/A')))))))	//Score -3 : if last transaction greater 3 months

Re: Calculated Dimension

sunny_talwar — Thu, 27 Oct 2016 19:17:20 GMT

Try this:

Aggr(if ($(vLastTransaction) < 8 ,3,

if ($(vLastTransaction) >= 8 and $(vLastTransaction) < 15 ,2,

if ($(vLastTransaction) >= 15 and $(vLastTransaction) < 22 ,1,

if ($(vLastTransaction) >= 22 and $(vLastTransaction) < 29 ,0,

if ($(vLastTransaction) >= 29 and $(vLastTransaction) < 60, -1,

if ($(vLastTransaction) >= 60 and $(vLastTransaction) < 90 ,-2,

if ($(vLastTransaction) >= 90 ,-3,'N/A'))))))), Customer)

Re: Calculated Dimension

albertestrada — Thu, 27 Oct 2016 19:22:26 GMT

THANK YOU!!! That worked