Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
albertestrada
Contributor II
Contributor II

Calculated Dimension

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,

1 Solution

Accepted Solutions
sunny_talwar

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)

Capture.PNG

View solution in original post

5 Replies
sunny_talwar

Try adding this:

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

sunny_talwar

or you can do this:

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

albertestrada
Contributor II
Contributor II
Author

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 transactionMax Date of Data SetminusMax Date per Customer
vLastTransactionMAX(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
FormulaComment
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
sunny_talwar

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)

Capture.PNG

albertestrada
Contributor II
Contributor II
Author

THANK YOU!!!  That worked