topic Re: Logic at script end -Derive Day level using MTD in QlikView

Logic at script end -Derive Day level using MTD

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to derive day level data using MTD. Below is the scenario,

1- We were having only MTD data.

2- To get day level data we need to do arithmetic calculation as MTD sales count on today - MTD sales count on Yesterday will get Sales for today.

EX: 5/11/2016 sales = MTD 5/11/2016 sale - MTD 5/10/2016 sales

Now we have to implement this logic at Script level.

May i know logic to write at script level.

Note: DATE = SYSDATE

Thanks..

Re: Logic at script end -Derive Day level using MTD

sunny_talwar — Wed, 11 May 2016 12:53:18 GMT

May be like this:

Table:

LOAD *,

MonthName(Date) as MonthYear;

LOAD * Inline [

Date, MTDSales

01/01/2016, 50

01/02/2016, 80

01/03/2016, 90

02/01/2016, 10

02/02/2016, 40

02/03/2016, 70

];

FinalTable:

LOAD *,

If(MonthYear = Peek('MonthYear'), MTDSales - Peek('MTDSales'), MTDSales) as Sales

Resident Table

Order By Date;

DROP Table Table;

Re: Logic at script end -Derive Day level using MTD

nareshthavidishetty — Wed, 11 May 2016 13:32:45 GMT

Hi,

This haven't gave day level data.Please find below image.

I need to derive one day data from MTD.

Thanks..

Re: Logic at script end -Derive Day level using MTD

sunny_talwar — Wed, 11 May 2016 13:34:23 GMT

If you add Date and Sales in a table box you will see the day level sales. If this is not what you want, may be elaborate and provide a sample with expected output

Re: Logic at script end -Derive Day level using MTD

Wed, 11 May 2016 13:52:14 GMT

Temp:

LOad * INline [

Date, Amount

05/11/2016, 250

05/10/2016, 220

05/09/2016, 200

05/08/2016, 150

];

Final:

Load *,

Amount-Previous(Amount) as NewAmount

Resident Temp

Order by Date Asc;

Drop table Temp;