Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Budget formula for the whole year

Hello together,

i have a formula for the Budget and it should always Show the whole Budget of the year. If I select a month, it should not Show the figures of the month, but the whole year.

Here is my formula

sum( {$<Nature={'Budget'}, [Fiscal Year]={'max([Fiscal year])'}>} Amount)


10 Replies
aveeeeeee7en
Valued Contributor III

Re: Budget formula for the whole year

By Pass Month Selection.

sum( {$<Nature={'Budget'}, [Fiscal Year]={'max([Fiscal year])'}, Month=>} Amount)

Not applicable

Re: Budget formula for the whole year

thank you for the formula.

but if i selected a month, the figures are changed.

I would have a figure which Change not the values, if I select a month.

Thanks.

Jörn

Re: Budget formula for the whole year

Hi

Try like this

sum( {$<Nature={'Budget'}, [Fiscal Year]={'max([Fiscal year])'}, Month=>} Amount)

Month= , excludes the month selection

{<MONTH=, YEAR = {2012}>} will get ALL months of the year 2012

Not applicable

Re: Budget formula for the whole year

The values are changed, i want one value for the whole year, without changing the values if I select a month.


MVP
MVP

Re: Budget formula for the whole year

Hi,

Try like this

sum( {$<MonthDimensionName=, Nature={'Budget'}, [Fiscal Year]={'max([Fiscal year])'}>} Amount)


MonthDimensionName= in the above expression ignores the selection of month in the expression


Note: Replace MonthDimensionName with your actual month dimension name.


Regards,

Jagan.

Not applicable

Re: Budget formula for the whole year

the values still changing.

Do you have other solution

vijayit43
New Contributor III

Re: Budget formula for the whole year

are You Provide Test QV For Help? because Month= , excludes the month selection in QV

MVP
MVP

Re: Budget formula for the whole year

Hi,

You have any other selections?  Whatever the fields you want not to change the value give it like in below in expression

FieldName1=, FieldName2=,......

OR thy this

sum( {1<Nature={'Budget'}, [Fiscal Year]={'max([Fiscal year])'}>} Amount)


will ignore all the field selections.


Hope this helps you.


Regards,

jagan.


MVP
MVP

Re: Budget formula for the whole year

The value will still change, because of the internal Max(). This aggregation function would take the selection into account. To avoid that include that set in the internal max() as well, like:

=sum( {$<Nature={'Budget'}, [Fiscal Year]={"$(=max({<Month>} [Fiscal year]))"}, Month=>} Amount)

Or, to ignore entire selection

=sum( {$<Nature={'Budget'}, [Fiscal Year]={"$(=max({1} [Fiscal year]))"}, Month=>} Amount)

Or, the ones you take under Sum()

Community Browser