Sum for specific day in pivot table

[Anonymous]

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.

Thanks in advance!

[sunny_talwar]

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)

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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.

[Anonymous]

Hi, anyone there help me please!

[Anonymous]

stalwar1‌ Could you help me a little bit more

[Quy_Nguyen]

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.

[Anonymous]

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!

[Quy_Nguyen]

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

Ex:  

Load * INLINE [

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.