# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## Sum for specific day in pivot table

Hi guys,

I have tasked to create a pivot table showing sum results by fixed days.

On a column, i have add some fixed days as:

=if(backup_date='31/01/2018' or backup_date = '28/02/2018' or backup_date = '31/03/2018', backup_date)

On a row, for the sum result, i have made a segment for a range of sum result of paid_money as:

=if(aggr(sum([paid_money]),CUST_KEY)<100000000,Dual('<100M',1),

if(aggr(sum(paid_money]),CUST_KEY)<300000000,Dual('100M-300M',2),

if(aggr(sum( [paid_money]),CUST_KEY)<500000000,Dual('300M-500M',3))))

The issue is the result of that segment will sum all the records of all backup_date till backup_date='31/01/2018' then from 31/01 to 28/02 while i only need to calculate exactly the sum of  paid_money in each day which i have added to to column like 30/01, 28/02 and .... Can someone guide me to solve this issue.

10 Replies
MVP

## Re: Sum for specific day in pivot table

May be you need this

Aggr(If(Sum([paid_money]) < 100000000, Dual('<100M',1),

If(Sum([paid_money]) < 300000000, Dual('100M-300M',2),

If(Sum([paid_money]) < 500000000, Dual('300M-500M',3)))), CUST_KEY, backup_date)

New Contributor II

## Re: Sum for specific day in pivot table

Hi Sunny,

Thanks for your helping but it doesn't work and sent out error message "calculated time out". I think that put backup_date in aggr function make qlik to calculate all backup_date then show result by my fixed backup_date.

I also have tried set backup_date = {//specific days} in aggr but doesn't work as well.

Could you give me some other options.

Regard.

MVP

## Re: Sum for specific day in pivot table

Not sure... may be I can look if you are able to share a sample?

New Contributor II

## Re: Sum for specific day in pivot table

Hi Sunny,

There is my sample:

Savedmoney_byCustomer

 Cust_key Contract_key Saved_money Backup_date 1 001A 25550000 30/01/2018 1 001A 25550000 31/01/2018 1 001B 150000 31/01/2018 1 001A 35550000 27/02/2018 1 001C 35550000 28/02/2018 2 002A 215550000 30/01/2018 2 002A 215550000 31/01/2018 2 002B 1150000 31/01/2018 2 002A 45550000 28/02/2018 3 003A 1500000 31/01/2018 3 003A 100000 28/02/2018

and my expected pivot table will show as:

 Backupdate 31/01/2018 28/02/2018 Ticket size Saved_money Saved_money 1B-5B 500M-1B 300M-500M 100M-300M <100M

The saved_money will be summed by each cust_key on the day i already fixed on a column and filtered by the segment asked above.

Thank you alot, Sunny.

New Contributor II

## Re: Sum for specific day in pivot table

Hi, anyone there help me please!

New Contributor II

## Re: Sum for specific day in pivot table

stalwar1‌ Could you help me a little bit more

Contributor III

## Re: Sum for specific day in pivot table

Hi,

I think you can use your dimension and Sunny's measure, it's correct. Try it on your sample data.

Maybe you get error message "calculated time out" because of your dataset is too large and your machine can not process it.

Or why dont you just create an other table which contains data for every month end, it will be a smaller dataset.

New Contributor II

## Re: Sum for specific day in pivot table

Hi, thank you for your reply. That mock data posted above is an example. Im tending to show the data for some days randomly, not only the month end. So is it impossible for another query, right?

Cheers!

Contributor III

## Re: Sum for specific day in pivot table

You can add a table with Backup_date, Flag from file or inline, and link it with your data:

Ex: