Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 :
attached qvw file
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))
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
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 |
what is the expected result of this table
=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
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))
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 🙂
OK 😉
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
];