Budget Vs Actual

[#1]

I'm still figuring out things in qlik. so need help.

I get data from oracle db and the table contains the following

SELECT seq_no,
account,
replace(substr(description,6,length(description)),':') description,
TYPE,
PRINT,
full_year_budget,
mtd_budget,
mtd_actual,
mtd_variance,
ytd_budget,
ytd_actual,
ytd_variance,
ltyd_actual,
period_name,
'20' || substr(period_name,5,2) period_year,
substr(period_name,1,3) period_month,
last_day('01-' || substr(period_name,1,3) || '-'|| '20' || substr(period_name,5,2)) as_of_ddmmyyyy
FROM <table Name>

where print = 'Y'
and type = 'T'
and type <> 'H';

uploading a sample data file, 

If the user selects ONLY month then I want to show the description, (mtd)budget, actual and variance for all years where month = selected month,

if the user selects ONLY year then I want to show description, (mtd)budget, actual and variance for all years in the table

if the user selects A month and Year then I want to show details for that Month and Year

[whiteline]

Hi.

You can make three different tables for each of your cases.

Than you can use Propeties->Layout->Show condition with some variables.

Finally, use macro for Document->Triggers->OnAnySelect to show and hide corresponding tables.

upd:

It's even easyer. You can use GetFieldSelections function.

So, to check if any Month is selected use =not isnull(GetFieldSelections(Month))

[#3]

Whiteline,

Do you have any sample on how to do this?

[whiteline]

What version of QV are you using ?

In 11 you can have show conditions on each dimension/expression. It's more useful and easyer.

[#5]

See the attached example file. It uses conditional layouts based on the field selections to show different graphs. You may need to adjust the fields in your expressions, as I wasn't sure what you wanted to display in each graph, but it gives you an example of the formatting.

[#6]

Thanks to both of you.

One question, When I try to have a dynamic lable for the colum header how to set the prior year value.

for example: 

=if (GetSelectedCount(PERIOD_YEAR), ' Budget- ' & GetFieldSelections(PERIOD_YEAR,', ',50) , '(N/A')

set the column header as Budget- 2012

how to display Budget - 2011

similarly, if user picks year 2011, i want a column header to be 2010 budget and next column header as 2011 budget

[#7 · Accepted solution]

Try this.