cross dimention

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

Yes, it's possible. See attached example

[#3]

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"?

[Gysbert_Wassenaar]

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.

[#5]

thank you!  i think i got it