Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

johnsmithqv
New Contributor III

command for Cummilative data into Daily totals

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

8 Replies
johnsmithqv
New Contributor III

Re: command for Cummilative data into Daily totals

any help pls

Re: command for Cummilative data into Daily totals

Hi John,

Do you have any sample?

It seems to be need to look on Peek function.

Like

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

[Movement Mileage],

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


johnsmithqv
New Contributor III

Re: command for Cummilative data into Daily totals

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

It will give cumulative data in date wise.

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

Digvijay_Singh
Honored Contributor III

Re: command for Cummilative data into Daily totals

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.

johnsmithqv
New Contributor III

Re: command for Cummilative data into Daily totals

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

johnsmithqv
New Contributor III

Re: command for Cummilative data into Daily totals

please respond

MVP
MVP

Re: command for Cummilative data into Daily totals

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;