Sum and aggr for PY

[waszcma1]

Hi to all,

Could you help me with this?  

sum(aggr(Cost_Transport, FCT_ExternalSalesCost_KEY))

it returns the cost as it should but i need previous year cost

so  I tried use this:

sum( {<Year{'2018'}>} aggr(Cost_Transport, FCT_ExternalSalesCost_KEY)) 

but it doesn't work the value is 0

I have  tried some solutions from forum but nothing works. 

[Taoufiq_Zarra]

can you share a sample data to be sure.

for last year use Year(today())-1 instead value.

or may be :

sum( {<Year={'2018'}>} aggr(sum( {<Year={'2018'}>} Cost_Transport), FCT_ExternalSalesCost_KEY)) 

[waszcma1]

Hi,

Well I cant provide example as data are confident but will prepare some fake example.

The problem is I am working on the report which wasn't designed by me and adding new measures (costs) from different fact table. It is too much work to rebuild whole data model.

The key used for aggregation is composite key of 6 dimensions.

The problem is in main fact table I think  I need to have a look what granularity is used 

I will try to go back with some example

Thank you for your input anyway 

[waszcma1]

ok so as an example: Cost transport for  selected year (2020) is 331,623552

sum( aggr(Cost_Transport,FCT_ExternalSalesCost_KEY))

I know that cost_Transport seems to be symmetric but this allocation is needed for inventserialid 

The problem with any previous time might be the FCT_ExternalSalesCost_KEY  

autonumber(invoiceIdMain&'|'&Selling_SDW_CompanyId&'|'&InvoiceDate&'|'&sdw_contractorid&'|'&CountryISO&'|'&sdw_productid&'|'&inventbatchid&'|'&inventbatchid) as FCT_ExternalSalesCost_KEY

As you could image the invoiceIdMain this is unique nuber for the invoice so in 2020 so if this numbers (like others dimensions ie.: inventbatchid, inventbatchid) not exists in 2019 or 2018

the formula works only for current selection  I think for the previous time i need different approach... but now I have "black hole in my brain"

when I select 2020 I am selecting data set  where invoiceIdMain are from 2020 I cant simply use set analysis for the same data set changing only 2020 into 2019 as the 2019 will contains totally different invoiceIdMain  and  now I understand that my formula shouldn't work.

Any Idea what formula could use aggregation on different time period based on actual selection?

Cost_Transport	FCT_ExternalSalesCost_KEY	Year	invoiceIdMain	%KEY_CompanyId	%KEY_DateId	%KEY_ContractorId	%KEY_CountryId	%KEY_ProductId	inventbatchid	inventserialid	total cost
61,2	1850568	2020	181005527	28	07-02-20	750069	ISR	778604	200206-113117549	PHI22470C6W	182,5
61,2	1850568	2020	181005527	28	07-02-20	750069	ISR	778604	200206-113117549	PHI22470C6W	196,0
61,2	1850569	2020	181005527	28	07-02-20	750069	ISR	778604	200206-113117549	C18106663000006045	182,6
61,2	1850569	2020	181005527	28	07-02-20	750069	ISR	778604	200206-113117549	C18106663000006045	196,0
104,6	1823303	2020	181005525	28	07-02-20	9634	ISR	918502	200205-113114141	PSZ21471GR8	551,4
104,6	1823303	2020	181005525	28	07-02-20	9634	ISR	918502	200205-113114141	PSZ21471GR8	602,2
104,6	1823304	2020	181005525	28	07-02-20	9634	ISR	918502	200205-113114141	PSZ214619S8	551,4
104,6	1823304	2020	181005525	28	07-02-20	9634	ISR	918502	200205-113114141	PSZ214619S8	602,2

[Taoufiq_Zarra]

@waszcma1 

what is the expected result of this table

[waszcma1]

=sum( aggr(Cost_Transport,FCT_ExternalSalesCost_KEY))

 will return 331,623552 and this is correct Transport Cost for the 2020

Check the table once again I have added total cost which allow to display all single records from fact table as you can see we have 4 lines per invoice and aggregation is used to sum only per inventserialid

so I couldn't use sum(distinct Cost_Transport) it will return wrong number too small

Sum(Cost_Transport) is also too big

well the same job will do this formula  =sum( aggr( Cost_Transport, inventserialid)) but still we need to use aggr function and the same problem is with set anlaysis

[Taoufiq_Zarra]

HI @waszcma1 

@waszcma1

I can't understand the request an the expected output.

if i use

=sum( aggr(Cost_Transport,FCT_ExternalSalesCost_KEY))

I get

and if I change in the sample I get

with :

sum( {<Year={'2018'}>} aggr(sum( {<Year={'2018'}>} Cost_Transport), FCT_ExternalSalesCost_KEY))

[waszcma1]

well I have attached only data set for 2020 I will add something from 2019 so then we should have enough data to test.

Thanks for that you trying to help me it is always good to talk to someone who know the Qlikview 🙂

[Taoufiq_Zarra]

OK 😉

[waszcma1]

table with example for 2019 and 2020

expected results for 2019

expected results for 2020

So what I would like to achieve is  an expression which shows total of Cost_Transport from 2019 grouped by FCT_ExternalSalesCost_KEY when we select Year 2020

so I can have one value  for 2020 = 3316

and  second value calculated dynamically based on year selection  so in this case 2019 = 445

Fact:
LOAD * Inline
[
Cost_Transport, FCT_ExternalSalesCost_KEY, Year, invoiceIdMain, %KEY_CompanyId, %KEY_DateId, %KEY_ContractorId, %KEY_CountryId, %KEY_ProductId, inventbatchid, inventserialid, totalcost
8 , 1360506, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,799651 ,190129-11395674 ,110361839 , 217
8 , 1360506, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,799651 ,190129-11395674 ,110361839 , 218
8 , 1360507, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,799651 ,190129-11395674 ,110361851 , 217
8 , 1360507, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,799651 ,190129-11395674 ,110361851 , 218
44 , 1357603, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,883671 ,190129-11395674 ,82284 , 1199
44 , 1357603, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,883671 ,190129-11395674 ,82284 , 1201
51 , 1391074, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,799666 ,190129-11395674 ,546819 , 1371
51 , 1391074, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,799666 ,190129-11395674 ,546819 , 1374
59 , 1362474, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,873582 ,190129-11395674 ,335760 , 1417
59 , 1362474, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,873582 ,190129-11395674 ,335760 , 1420
59 , 1362475, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,873582 ,190129-11395674 ,335627 , 1417
59 , 1362475, 2019 ,15135925 ,14 ,04-02-19 ,229596 ,BGR ,873582 ,190129-11395674 ,335627 , 1420
108 , 1369969, 2019 ,15135885 ,14 ,01-02-19 ,191629 ,BGR ,889248 ,190123-43334881 ,S463NF0KC12336 , 5567
108 , 1369969, 2019 ,15135885 ,14 ,01-02-19 ,191629 ,BGR ,889248 ,190123-43334881 ,S463NF0KC12336 , 5575
108 , 1369970, 2019 ,15135885 ,14 ,01-02-19 ,191629 ,BGR ,889248 ,190123-43334881 ,S463NF0KC12329 , 5567
108 , 1369970, 2019 ,15135885 ,14 ,01-02-19 ,191629 ,BGR ,889248 ,190123-43334881 ,S463NF0KC12329 , 5575
612 , 1850568, 2020 ,181005527 ,28 ,07-02-20 ,750069 ,ISR ,778604 ,200206-113117549 ,PHI22470C6W , 1825
612 , 1850568, 2020 ,181005527 ,28 ,07-02-20 ,750069 ,ISR ,778604 ,200206-113117549 ,PHI22470C6W , 1960
612 , 1850569, 2020 ,181005527 ,28 ,07-02-20 ,750069 ,ISR ,778604 ,200206-113117549 ,C18106663000006045 , 1826
612 , 1850569, 2020 ,181005527 ,28 ,07-02-20 ,750069 ,ISR ,778604 ,200206-113117549 ,C18106663000006045 , 1960
1046 , 1823303, 2020 ,181005525 ,28 ,07-02-20 ,9634 ,ISR ,918502 ,200205-113114141 ,PSZ21471GR8 , 5514
1046 , 1823303, 2020 ,181005525 ,28 ,07-02-20 ,9634 ,ISR ,918502 ,200205-113114141 ,PSZ21471GR8 , 6022
1046 , 1823304, 2020 ,181005525 ,28 ,07-02-20 ,9634 ,ISR ,918502 ,200205-113114141 ,PSZ214619S8 , 5514
1046 , 1823304, 2020 ,181005525 ,28 ,07-02-20 ,9634 ,ISR ,98502 ,200205-113114141 ,PSZ214619S8 , 6022

];