Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have source table like this
authority | head | fiscal_ year | type_id | commited_amt | expense |
---|---|---|---|---|---|
IA | 441 | 2002-2003 | L | 12500 | 5000 |
IA | 441 | 2003-2004 | L | 1500 | 500 |
IA | 441 | 2009-2010 | F | $500 | $100 |
IA | 441 | 2010-2012 | F | $6555 | $1200 |
IA | 441 | 2012-2013 | L | 15000 | 3500 |
IA | 441 | 2013-2014 | L | 2000 | 100 |
I ONLY WANT TO SHOW A PIVOT TABLE IN QLIKVIEW
with all records of type_id= 'L' and Fiscal year except maximum which is 2013-2014 in my case and
note:
max_year is a variable having value 2013-2014 in it
KINLDY give a expression to achive in a pivote table
having a dimenions
=authority
=head
=fiscal_year
=type_id
expression
for commited amt is =sum({$<type_id={'L'}>}commited_amt)
for expense is =sum({$<type_id={'L'}>}expense_amt)
how to show data not including 2013-2014 row ???
regards
Fady
Shariq
Try replacing the standard dimension fiscal_year with a calculated dimension like::
if ( fiscal_year = '2013-2014' , null() )
And do remember to check the checkbox "Suppress when value is null".
Best Regards, Bill
Add a constraint for fiscal_year to the set modifier:
sum({$<type_id={'L'},[fiscal_ year]-={'$(max_year)'}>}commited_amt)
See attached example.
Hi
when we use '-=' it exclude data
like this:
sum({$<type_id={'L'},[fiscal_ year]-={'2013-2014'}>}commited_amt)
this expression calculate commited_amt for all row except rows that have fiscal_ year=2013-2014
Best Regards