Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
)))))
,[Gifts Id] )
-Rob
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...
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
)))))
,[Gifts Id] )
-Rob
Rob,
Hey, thanks, that worked... I thought I tried that... but either left a count() in, or had a syntax error...
So, thanks,
Jarrell