Rangesum with Peek for Timestamp dimension

[SunainaUmberkar]

Hello All, I need to do a cumulation of a column based on timestamp, servername, licname, groupname. Data is as below. For each timestamp, like for ex 3/14/2022 17:00:12- this timestamp has 2 values in Token column like 1612 and 3824. I want to do cumulation of this 2 and for next 3/14/2022 17:15:11 it should display the cumulation of 1612+3824 etc. So basically based on timestamp column cumulation to be done and then maximum value of that hour must be displayed. The level of hierarchy of data is Timestamp,ServerName,Licname,Groupname. i Tried using below peek with rangesum function in script, but it is not working. A: load * from Table1; B: Noconcatenate Load TimeStamp, ServerName, Licname, GroupName, Token, If(TimeStamp=Peek('TimeStamp') ,Token+Peek('CUMUL'),Token) AS CUMUL Resident A Order by DSLSTimeStamp, DSLSServerName, DSLSLicenseName, DSLSGroupName; Date TimeStamp Hour ServerName Licname Groupname Token 2022-03-14 3/14/2022 17:00:12 17 A K France 1612 2022-03-14 3/14/2022 17:00:12 17 A K France 3824 2022-03-14 3/14/2022 17:15:11 17 A K France 1612 2022-03-14 3/14/2022 17:15:11 17 A K France 3824 2022-03-14 3/14/2022 17:30:11 17 A K France 1612 2022-03-14 3/14/2022 17:30:11 17 A K France 3442 2022-03-14 3/14/2022 17:30:11 17 A K France 3824 2022-03-14 3/14/2022 17:45:11 17 A K France 1612 2022-03-14 3/14/2022 17:45:11 17 A K France 3442 2022-03-14 3/14/2022 17:45:11 17 A K France 3824 Can someone please assist on this. Thanks in advance.

[NitinK7]

can you attach your sample data with output what you want

[SunainaUmberkar]

Hello Nitin,

Yes, please find the sample data attached in Excel. The last column Cumulation is the output to be expected.

Here, the summation is done based on timestamp column. Also, a groupname column has differernt countries, so for the same server, licname for each country summation has to be done.

Thanks in advance.

[NitinK7 · Accepted solution]

Please find the attached .QVF file

may be that will help to you

[SunainaUmberkar]

Hello Nitin,

The solution is working perfectly fine. 

I just need one more help wrt to below scenario.

The scenario is same as above, But i need to cumulative based on GroupName. I have attached the excel with sample data. Like for a timestamp, a servername, a licensename, there are various Group Names.

So the cumulation should be done on Group Level. Like for France and germany we have values as in Excel. It should cumulate the 2 values of france and germany. Although capacity for each timestamp, each servername,licname,groupname is same. At different group name levels, the capacity value is different. 

For example, in the excel, for 3/14/2022 5:45:11 PM timestamp, Servername A, Licname K, Group name France has a value 2 and germany 5, so basically cumulation shld be 2+5 which 7.

Please assist on this. 

[SunainaUmberkar]

Hello Everyone,

Kindly assist on above. Thanks in advance.

[NitinK7 · Accepted solution]

please find the attached .QVF file

[SunainaUmberkar]

Hello Nitin,

Thanks for your response. Above solution is working fine.