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: 
Anonymous
Not applicable

Display Sum YTD actual and Future budget on pivot table

Hi All,

Hopefully this is doable but I'm drawing a blank on displaying actual and budget numbers on a pivot table.

So here's my scenario:

I have a pivot table with a dimension(account) and two measures(Amount(YTD) and Amount(FD)), the requirement is to show sum of actual for past accounting periods and sum of budget for future accounting periods.

Plotting accounts vs both measures gives a pivot table with $0.00 columns and data handling option doesn't seem to work, Is there a way to suppress the zero columns?

Both measures are calculated as;

Amount(YTD Actual): Sum({<[Acc Period]={"<$(vCurrentMonth)"},Source={"Actual"}>}[Actual Amount])

Amount(FD Budget): Sum({<[Acc Period]={">=$(vCurrentMonth)"},Source={"Budget"}>}[Budget Amount])

I've also tried creating a calculated dimension to show only the applicable accounting periods but it's not giving expected results either.

Requirement;

if accounting period < current month, sum(Actual Amount)

if accounting period >= current month, sum(budget amount)

Please advice.

Thank you

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

All you need is to add both the expression.

Have a look at the attachment.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

2 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

All you need is to add both the expression.

Have a look at the attachment.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

The suggestion works great! Thanks