Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Did you mean:
Creator

## Help with calculated dimension

Hey everyone,

- I want to use a calculated dimension... and show counts.  Attached is a sample...if I export to MS Excel and add a pivot table, I can see this:

by setting MS Excel to this:

How can I do this in Qlikview?

- I don't want to do it in a LOAD statement, because I want users to be able to select dimensions, and the chart/table calculates the values per user selections [Note:  I did not include dimensions in the sample]....

- Do I use Aggr()?   I've tried this in an expression [per the formula, aggr(expression, dimensions) ]...but not sure, then what dimension to use?

= aggr( COUNT([Gifts Id]),

if (  max([Gift FY])=\$(vFiscalYearCurrent), 'Current',

if ( (max([Gift FY])=\$(vFiscalYearCurrent)-1) and (Min([Gift FY])=\$(vFiscalYearCurrent)-1), 'LYBNT-1st Time',

if ( (max([Gift FY])=\$(vFiscalYearCurrent)-1) and (Min([Gift FY])<\$(vFiscalYearCurrent)-1), 'LYBNT',

if ( (max([Gift FY])>=\$(vFiscalYearCurrent)-6) AND (max([Gift FY])<=\$(vFiscalYearCurrent)-2), 'SYBNT',

if ( (max([Gift FY])<\$(vFiscalYearCurrent)-6), 'Lapsed Donor', 0

)))))

)

LYBNT = A Donor who gave (L)ast (Y)ear (B)ut (N)ot (T)his year...

SYBNT = A Donor who gave (S)ome (Y)ear (B)ut (N)ot (T)his year...

Any help would be most appreciated...

Jarrell

1 Solution

Accepted Solutions

I think your Dim can just aggr() over [Gifts Id}

= aggr(

if (  max([Gift FY])=\$(vFiscalYearCurrent), 'Current',

if ( (max([Gift FY])=\$(vFiscalYearCurrent)-1) and (Min([Gift FY])=\$(vFiscalYearCurrent)-1), 'LYBNT-1st Time',

if ( (max([Gift FY])=\$(vFiscalYearCurrent)-1) and (Min([Gift FY])<\$(vFiscalYearCurrent)-1), 'LYBNT',

if ( (max([Gift FY])>=\$(vFiscalYearCurrent)-6) AND (max([Gift FY])<=\$(vFiscalYearCurrent)-2), 'SYBNT',

if ( (max([Gift FY])<\$(vFiscalYearCurrent)-6), 'Lapsed Donor', 0

)))))

-Rob

http://robwunderlich.com

4 Replies
Anonymous
Not applicable

I think you can use ValueList() for calculated dimension.  I'm attaching an example that shows how to use ValueList().  You'll have to replace 1,2,3,4,5 with the appropriate calculations.  I assume they will be something like:

count(distinct if(<some conditions>, "Gifts Id"))

You probably need to aggr by Donor in the "some conditions" part...

Creator
Author

Thanks.. but I'm not sure what aggr() to use... hence, my question.

I think your Dim can just aggr() over [Gifts Id}

= aggr(

if (  max([Gift FY])=\$(vFiscalYearCurrent), 'Current',

if ( (max([Gift FY])=\$(vFiscalYearCurrent)-1) and (Min([Gift FY])=\$(vFiscalYearCurrent)-1), 'LYBNT-1st Time',

if ( (max([Gift FY])=\$(vFiscalYearCurrent)-1) and (Min([Gift FY])<\$(vFiscalYearCurrent)-1), 'LYBNT',

if ( (max([Gift FY])>=\$(vFiscalYearCurrent)-6) AND (max([Gift FY])<=\$(vFiscalYearCurrent)-2), 'SYBNT',

if ( (max([Gift FY])<\$(vFiscalYearCurrent)-6), 'Lapsed Donor', 0

)))))

-Rob

http://robwunderlich.com

Creator
Author

Rob,

Hey, thanks, that worked... I thought I tried that... but either left a count() in, or had a syntax error...

So, thanks,

Jarrell

Community Browser