topic Re: command for Cummilative data into Daily totals in QlikView

command for Cummilative data into Daily totals

Anonymous — Tue, 28 Jun 2016 03:38:08 GMT

Hi all,.

I need some inputs from you where i need to create a command that makes cumulative data into daily totals ?

Is there such a command?

attached is the sample Qlikview and Excel file to look at it

Re: command for Cummilative data into Daily totals

Anonymous — Tue, 28 Jun 2016 03:50:54 GMT

any help pls

Re: command for Cummilative data into Daily totals

settu_periasamy — Tue, 28 Jun 2016 04:04:50 GMT

Hi John,

Do you have any sample?

It seems to be need to look on Peek function.

Date(Floor([Movement Date Time])) as [Data Date],

[Movement Mileage],

if(RowNo()=1,[Movement Mileage],[Movement Mileage]+Peek([Daily Movement]))as [Daily Movement]

Re: command for Cummilative data into Daily totals

Anonymous — Tue, 28 Jun 2016 04:06:50 GMT

Hi Settu,

thanks for the reply

could u pls breakdpwn the expression what we are achieving with this?

Re: command for Cummilative data into Daily totals

settu_periasamy — Tue, 28 Jun 2016 04:16:36 GMT

It will give cumulative data in date wise.

If you provide the sample, will try to provide the better suggestion..

Re: command for Cummilative data into Daily totals

Digvijay_Singh — Tue, 28 Jun 2016 04:38:23 GMT

If you have data for more than 1 day and you want separate total for each day, you may need to check if previous date is equal to current date, then you can add day checking to suggest solution by Settu -

Date(Floor([Movement Date Time])) as [Data Date],

[Movement Mileage],

if(RowNo()=1,[Movement Mileage],

If(Previous([Data Date])=[Data Date], [Movement Mileage]+Peek([Daily Movement],[Movement Mileage])) as [Daily Movement]

1. If we are at the top row of the table, then just take the value of that row into [Daily Movement].

2. If we are on 2nd or further rows, then first check if same day record exists, if yes then add previous row Movement Mileage( calculated in first step) to current row Movement Mileage

3. If day is changed then again start afresh, and take current row movement mileage to dailly movement.

Re: command for Cummilative data into Daily totals

Anonymous — Tue, 28 Jun 2016 08:54:09 GMT

Thanks all for the responses

Please look into the attached source file where we need to apply logic for cumulative to daily records data

please help me please

Re: command for Cummilative data into Daily totals

Anonymous — Tue, 28 Jun 2016 09:07:00 GMT

please respond

Re: command for Cummilative data into Daily totals

MK_QSL — Tue, 28 Jun 2016 09:37:06 GMT

Input:
LOAD Reg, 
     [Date and Time], 
     Date, 
     Reg & Date as Key,
     Cum
FROM
[Cumulative Example.xlsx]
(ooxml, embedded labels, table is Sheet1);


Left Join (Input)
Load Key, FirstSortedValue(Cum, -[Date and Time]) - FirstSortedValue(Cum, [Date and Time]) as Daily Resident Input
Group By Key;


Drop Field Key;