Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
okolyug16
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
kaushik_solanki
Not applicable

Re: Display Sum YTD actual and Future budget on pivot table

Hi,

All you need is to add both the expression.

Have a look at the attachment.

Regards,

Kaushik Solanki

2 Replies
kaushik_solanki
Not applicable

Re: Display Sum YTD actual and Future budget on pivot table

Hi,

All you need is to add both the expression.

Have a look at the attachment.

Regards,

Kaushik Solanki

okolyug16
Not applicable

Re: Display Sum YTD actual and Future budget on pivot table

The suggestion works great! Thanks