Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Community,
I searched the community discussion for a solution to my problem but up to now did not find a sufficient answer. I am quite a Newbie with QV.
My starting position:
From one very small company of our business group I get an Excel file with a weekly update of sales.
The dimensions are Code and Date. Values are kg and Amount
Code | Date | kg | Amount |
---|---|---|---|
ABC | 05.08.2016 | 25 | 250 |
DEF | 05.08.2016 | 50 | 500 |
GHI | 05.08.2016 | 25 | 500 |
The following week I get the accumulated values for two weeks:
Code | Date | kg | Amount |
---|---|---|---|
ABC | 12.08.2016 | 50 | 550 |
DEF | 12.08.2016 | 75 | 700 |
GHI | 12.08.2016 | 25 | 500 |
JKL | 12.08.2016 | 50 | 1000 |
I should report only sales by week:
Code | Date | kg | Amount |
---|---|---|---|
ABC | 12.08.2016 | 25 | 300 |
DEF | 12.08.2016 | 25 | 200 |
GHI | 12.08.2016 | 0 | 0 |
JKL | 12.08.2016 | 50 | 1000 |
I read about peek() and previous() but the examples I found only work with a single dimension.
How can I solve that?
Thanks in advance
Stephan Zwick
Liebe Community,
ich habe schon die Diskussionen durchsucht aber bisher keine Lösung gefunden. Ich bin ein Anfänger was QV betrifft.
Augangslage. Von einer sehr kleinen Gesellschaft in unserem Konzern erhalte ich jede Woche eine Excel Datei mit Verkaufszahlen. Diese kommen als kumulierte Zahlen daher. Gewünscht sind jedoch die wöchentlichen Veränderungen.
Die von mir gefundenen Beiträge zu previous und peek gehen (nach meinem Verstandnis) immer von einer einzigen Dimension aus.
Irgendwelche Ideen?
Vielen Dank
Stephan Zwick
It's a slight different approach without peek and previous to get the data on a weekly-level and not only the differences between the current and last data. I think it will be suitable for your requirement than now you could simply select your data:
t1:
LOAD Code, Date as LastDate, kg as kg_cum, Amount as Amount_cum
FROM [https://community.qlik.com/thread/230210] (html, codepage is 1252, embedded labels, table is @2);
left join (t1)
LOAD Code, Date, kg, Amount
FROM [https://community.qlik.com/thread/230210] (html, codepage is 1252, embedded labels, table is @1);
t2:
load Code, LastDate as Date, alt(kg_cum - kg, kg_cum) as kg, alt(Amount_cum - Amount, Amount_cum) as Amount resident t1;
concatenate(t2)
LOAD Code, Date, kg, Amount
FROM [https://community.qlik.com/thread/230210] (html, codepage is 1252, embedded labels, table is @1);
drop tables t1;
- Marcus
It's a slight different approach without peek and previous to get the data on a weekly-level and not only the differences between the current and last data. I think it will be suitable for your requirement than now you could simply select your data:
t1:
LOAD Code, Date as LastDate, kg as kg_cum, Amount as Amount_cum
FROM [https://community.qlik.com/thread/230210] (html, codepage is 1252, embedded labels, table is @2);
left join (t1)
LOAD Code, Date, kg, Amount
FROM [https://community.qlik.com/thread/230210] (html, codepage is 1252, embedded labels, table is @1);
t2:
load Code, LastDate as Date, alt(kg_cum - kg, kg_cum) as kg, alt(Amount_cum - Amount, Amount_cum) as Amount resident t1;
concatenate(t2)
LOAD Code, Date, kg, Amount
FROM [https://community.qlik.com/thread/230210] (html, codepage is 1252, embedded labels, table is @1);
drop tables t1;
- Marcus
Thank you!
At least now I know that previous()/peek() is not suitable.