Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
SeanM
New Contributor

Set Analysis - Using Month, Year to Date and Annual Figure -wrongly displaying all months

I want display some financial information for the current month and the annual budget total in a chart (Straight Table)-

I want to display for individual accounts; the selected month; the year to date value for that month and also the annual value

One month has been selected and the total values coming out are correct but the  table is displaying  data for all 12 months - it is including each budget month individually rather than the total budget for the year

If I remove the  month (GLPeriod.PeriodNum) dimension then the data is displayed correctly but I do need to include this field.

This expression displays the year to date budget for the chosen month:

=Sum({$<GLBal.BudgetName={'$(vCurrentBudget)'}, GLSubLedg.Currency ={"GBP"}>} [GLBal.YTDBudget])

This expression should displays the annual budget not dependant on the chosen month:

Sum({$<GLBal.BudgetName={'$(vCurrentBudget)'},[GLPeriod.PeriodNum]=,GLSubLedg.Currency ={"GBP"}>} [GLBal.BudgetAmount])

I have also tried the cumulative budget figure  specified as month 12 - this approach then displays the selected month and month 12:

Sum({$<GLBal.BudgetName={'$(vCurrentBudget)'},[GLPeriod.PeriodNum]={$(=12)},GLSubLedg.Currency ={"GBP"}>} [GLBal.YTDBudget])

 

Can anyone please point out where I am going wrong

Thanks

 

 

 

 

 

Tags (2)
1 Reply
Partner
Partner

Re: Set Analysis - Using Month, Year to Date and Annual Figure -wrongly displaying all months

For annual Sum of Budget, you need to indicate that you want to sum of the max year present in the dataset

Sum({<Year = {Max(Year)}>}[Budget])

For the monthly sum 

Sum({<Month = {Max(Month)}>}[Budget])

 

Regards,

Mohamed