topic Sum of Sales Qty between Two Dates(FromDate and ToDate) in Adhoc Report in QlikView

Sum of Sales Qty between Two Dates(FromDate and ToDate) in Adhoc Report

Raggs — Tue, 04 May 2021 07:24:59 GMT

Hello Team,

I have data like below and Its linked with Sales table with the key combination of 3 Fields i.e. Customer Cat1, Item#, and Year(Year derived from FromDate field)

I need to calculate Markdown value on Sales Qty which is in between FromDate and Todate.

Example: Customer Cat1(370665) and Item#(38725-143)

For the above example we have FromDate(01-02-2020(MM-DD-YYYY)) and Todate(31-07-2020(MM-DD-YYYY)) and the Amount should calculate from the Amount filed, i.e. 9.00

Example: Need to calculate the Markdown Rs. 9.00 is on Sales Qty which is in between FromDate(01-02-2020(MM-DD-YYYY)) and Todate(31-07-2020(MM-DD-YYYY))

I have used the below Expression, however its not working.

Sum({< YearPeriod = {$(=Max(RequestYearPeriod)-1)}, InvoiceDate={" >= (FromDate) <= (ToDate) "} >} Qty) * Amount

Can any one help me on this with high priority.

Regards and Thanks,

Raghu

@swuehl @sunny_talwar

Re: Sum of Sales Qty between Two Dates(FromDate and ToDate) in Adhoc Report

Dalton_Ruer — Wed, 05 May 2021 17:26:41 GMT

The expression editor should display what it will evaluate to at the bottom of the screen. Not sure if you need $(FromDate) perhaps or '$(FromDate)' to ensure the dates have single quotes around them. The Expression editor syntax at the bottom of the screen will display what is wrong and that usually helps especially since you already understand Set Analysis.

Re: Sum of Sales Qty between Two Dates(FromDate and ToDate) in Adhoc Report

Raggs — Wed, 12 May 2021 06:43:00 GMT

Hello @Dalton_Ruer

I have tried this, however its not working

Can any one help me on this ASAP

regards,

Raghu