Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have table:
Country | month | Sum (Sale) |
England | 201208 | 24971 |
England | 201209 | 25826 |
England | 201210 | 24560 |
England | 201211 | 15207 |
Japan | 201208 | 6481 |
Japan | 201209 | 5705 |
Japan | 201210 | 6106 |
Japan | 201211 | 2699 |
from this table i want make like this table:
Country | period 3 month | Sum (Sale) |
England | 201210 & 201209 & 201208 | 75357 |
England | 201211 & 201210 & 201209 | 65593 |
Japan | 201210 & 201209 & 201208 | 18292 |
Japan | 201211 & 201210 & 201209 | 14510 |
Is posseble create dimention like this way? Problem that a have '201209' in bouth periods.
Yes, it's possible. See attached example
Yes, it's possible. See attached example
Hi, Gysbert!
Thank you for your help.
I am trying to understand your example step by step.
1. i have original data such as:
Country | Sale | pmonth |
England | 25 826 | 201209 |
England | 24 560 | 201210 |
England | 15 207 | 201211 |
i geting them from sql store procedure like
SQL SELECT *
FROM "LD_StoreProc".dbo.testIP;
2. i have to adding for every p month in my data new field wich aggrigate 3 mont period?
Country | Sale | pmonth | month |
England | 28 343 | 201207 | 201209 |
England | 24 971 | 201208 | 201209 |
England | 25 826 | 201209 | 201209 |
England | 24 971 | 201208 | 201210 |
England | 25 826 | 201209 | 201210 |
England | 24 560 | 201210 | 201210 |
and i have to do it before load into QV in my "LD_StoreProc"?
Try this to create T1:
T1:
load Country,month,SumSale,
Monthstart(date#(month&'01','YYYYMMDD')) as mstart;
SQL SELECT * FROM "LD_StoreProc".dbo.testIP;
Make sure you use the correct field names. Qlikview is case sensitive, so Country is not the same field as country.
The rest can stay the same. It first creates three pmonth records for every month in the original data. This is where the while clause and the iterno() function are used. The result of that is then joined on the original data. The rest is cleaning up and sorting the resulting table.
thank you! i think i got it