Qty calculation based on From date and To date

Raggs — Fri, 30 Apr 2021 16:30:16 GMT

Hello Team,

I have data like below

I need to calculate Markdown on Particular Item Qty which is in between the FromDate and ToDate.

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

Can any one help me on this with high priority.

Regards and Thanks,

Raghu

Re: Qty calculation based on From date and To date

edwin — Mon, 03 May 2021 15:14:48 GMT

if i understand this correctly, you want to look up the field AMount from the first table.

since the amount based on a date will not change no matter what filter is used, you can determine this in your load script so that your visualization is more efficient

on high level, left join your fact table with your pricing table (since there is no detail around the relationship between the pricing table and the order table - assume you look it up only by item number)
join using item number, amount, start date, and end date
then cleanup the resulting table using teh where clause Date >= start date and Date <=end date
this will leave you with a new column in your fact table which is Price (renamed amount).
so in your expression you just need sum(Qty * Price)
of course you need to rename fields and drop those you dont need in the fact table

topic Qty calculation based on From date and To date in QlikView

Qty calculation based on From date and To date

Re: Qty calculation based on From date and To date