10 Replies Latest reply: Jul 3, 2018 3:57 AM by phuc pham

# 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.

• ###### 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)

• ###### 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.

• ###### Re: Sum for specific day in pivot table

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

• ###### 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.

• ###### Re: Sum for specific day in pivot table

stalwar1 Could you help me a little bit more

• ###### Re: Sum for specific day in pivot table

Hi, anyone there help me please!

• ###### 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.

• ###### 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!

• ###### 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:

Backup_date , Flag

31/01/2018 ,1

28/02/2018 ,1

];

Then use the Flag in your expression. For detail, please have a look at attached app.

If your machine still can not process the data (calculated time out), you need to aggregate/categorize your data in script load.

• ###### Re: Sum for specific day in pivot table

Thank you for your recommendation but i dont have right to access to the script load so i think will collect the data manually day by day.

Cheers!