Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Raggs
Contributor III
Contributor III

Qty calculation based on From date and To date

Hello Team,

I have data like below 

Raggs_0-1619799763954.png

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. 

Raggs_1-1619800167893.png

Can any one help me on this with high priority. 

 

Regards and Thanks,

Raghu

1 Reply
edwin
Master
Master

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