Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Budget Vs Actual

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

1 Solution

Accepted Solutions
Not applicable

Re: Budget Vs Actual

Try this.

6 Replies
whiteline
Honored Contributor II

Re: Budget Vs Actual

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))

Not applicable

Re: Budget Vs Actual

Whiteline,

Do you have any sample on how to do this?

whiteline
Honored Contributor II

Re: Budget Vs Actual

What version of QV are you using ?

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

Not applicable

Re: Budget Vs Actual

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.

Not applicable

Re: Budget Vs Actual

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

Not applicable

Re: Budget Vs Actual

Try this.

Community Browser