Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

6 Replies
whiteline
Master II
Master II

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
Author

Whiteline,

Do you have any sample on how to do this?

whiteline
Master II
Master II

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
Author

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
Author

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
Author

Try this.