Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Thanks in advance!

10 Replies
MVP
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)

phucpv9119
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
MVP

Re: Sum for specific day in pivot table

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

phucpv9119
New Contributor II

Re: Sum for specific day in pivot table

Hi Sunny,

There is my sample:

Savedmoney_byCustomer  

    

Cust_keyContract_keySaved_moneyBackup_date
1001A2555000030/01/2018
1001A2555000031/01/2018
1001B15000031/01/2018
1001A3555000027/02/2018
1001C3555000028/02/2018
2002A21555000030/01/2018
2002A21555000031/01/2018
2002B115000031/01/2018
2002A4555000028/02/2018
3003A150000031/01/2018
3003A10000028/02/2018

and my expected pivot table will show as:

     

Backupdate31/01/201828/02/2018
Ticket sizeSaved_moneySaved_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.

phucpv9119
New Contributor II

Re: Sum for specific day in pivot table

Hi, anyone there help me please!

phucpv9119
New Contributor II

Re: Sum for specific day in pivot table

stalwar1‌ Could you help me a little bit more

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

phucpv9119
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!

Quy_Nguyen
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:  

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.

Community Browser