Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
waszcma1
Creator
Creator

Sum and aggr for PY

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. 

 

1 Solution

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

@waszcma1 

you already have the FCT_ExternalSalesCost_KEY field that's uniqu, so 

if I select 2020 (Max year) : total of Cost_Transport from 2019 grouped by FCT_ExternalSalesCost_KEY when we select Year 2020

sum({<Year={"> $(=Max(Year)-1)"}>}  aggr(Max({<Year={"> $(=Max(Year)-1)"}>} Cost_Transport),FCT_ExternalSalesCost_KEY))

 

Current selected Year -> Maxyear

sum({<Year={"$(=Max(Year))"}>}  aggr(Max({<Year={"$(=Max(Year))"}>} Cost_Transport),FCT_ExternalSalesCost_KEY))

 

Year-1 : If I selected 2020 return 2019 value :

sum({<Year={"$(=Max(Year)-1)"}>}  aggr(Max({<Year={"$(=Max(Year)-1)"}>} Cost_Transport),FCT_ExternalSalesCost_KEY))

 

 

and so on.

output :

Capture.JPG

 

attached qvw file

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

12 Replies
Taoufiq_Zarra
Master II
Master II

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)) 
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
waszcma1
Creator
Creator
Author

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
Creator
Creator
Author

 

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,2185056820201810055272807-02-20750069ISR778604200206-113117549PHI22470C6W182,5
61,2185056820201810055272807-02-20750069ISR778604200206-113117549PHI22470C6W196,0
61,2185056920201810055272807-02-20750069ISR778604200206-113117549C18106663000006045182,6
61,2185056920201810055272807-02-20750069ISR778604200206-113117549C18106663000006045196,0
104,6182330320201810055252807-02-209634ISR918502200205-113114141PSZ21471GR8551,4
104,6182330320201810055252807-02-209634ISR918502200205-113114141PSZ21471GR8602,2
104,6182330420201810055252807-02-209634ISR918502200205-113114141PSZ214619S8551,4
104,6182330420201810055252807-02-209634ISR918502200205-113114141PSZ214619S8602,2
Taoufiq_Zarra
Master II
Master II

@waszcma1 

what is the expected result of this table

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
waszcma1
Creator
Creator
Author

=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
Master II
Master II

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

Capture.JPG

and if I change in the sample I get

Capture.PNG

with :

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
waszcma1
Creator
Creator
Author

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
Master II
Master II

OK 😉

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
waszcma1
Creator
Creator
Author

table with example for 2019 and 2020

expected results for 2019

aggr v1.JPG

expected results for 2020

aggr v2.JPG

 

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

];