topic Re: Changing date values in expressions in QlikView

Changing date values in expressions

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

I have a reporting issue which I hope you chaps will be able to assist with.

My data model has three streams of data which each have a unique date - there is a master calendar created with date type (such as Canonical Date) - the three dates are inwards_date, production_date and delivery_date.

I have a report where I need to use data from the production table and the sales table and report against the delivery_date (such as below)

=sum( //raw material cost from production for product sold

(sum({$<

Customer, // from sales table

[Production Consignment Line] = P([Production Consignment Line]), // from production table but matches data in sales table

$(function.clear_selections(dimension.calendar)) //function to remove all date filters to allow production data to return if different date

[Production Cost Raw Material Amount]) //from production table

SUM( [Sales Quantity]) // from sales table

This expression is working correctly and returning the right result - unfortunately it is returning the results according to the delivery_date for the sales table data for the sales element and the production_date for the production table data.

for example if production was on day 1 and the item was despatched on day 2 then my results look like the below (for the 11/09/2016 the results are 0*88, for the 12/09/2016 the results are 454.08*0)

I need to report against delivery_date. Is there a way to force / change the date IN THE EXPRESSION so that the production data is returned against delivery_date.

Please note that I do not want to amend the data nor the script itself as I need to report against production date in other areas, I just want to amend it in this specific expression.

Re: Changing date values in expressions

sunny_talwar — Wed, 19 Oct 2016 17:03:22 GMT

Do you have flags created for individual date fields in your script? May be you can add the date_type_flag to your expression?

Re: Changing date values in expressions

stig1984 — Thu, 20 Oct 2016 07:50:14 GMT

We do have the flags.

The link calendar has the field 'Date Type' with flags for 'Delivery Date' (sales date) and 'Production Date' (for production). I need the expression to either:

a) ignore production date so that all data is returned against delivery date

b) change the production date to match the delivery date.

I have tried it with the set modifier [Date Type] = {'Delivery Date'}, in the expression against each element and just against the sum(sales qty) but with the same result as above.

Re: Changing date values in expressions

sunny_talwar — Thu, 20 Oct 2016 16:24:38 GMT

Difficult to say what might be wrong without looking at a sample, would you be able to share one?

Re: Changing date values in expressions

stig1984 — Mon, 24 Oct 2016 09:18:18 GMT

Unfortunately there is a lot of sensitive data in the model so I'll need to try to build a replica and mirror the issue first.

Re: Changing date values in expressions

sunny_talwar — Mon, 24 Oct 2016 14:45:40 GMT

See if this link helps:

Preparing examples for Upload - Reduction and Data Scrambling

Re: Changing date values in expressions

stig1984 — Wed, 26 Oct 2016 10:49:44 GMT

Thanks for your assistance and especially for the link above - this will be really useful for the future.

We've worked around this issue by using by aggr() function by production consignment line such as below. Needed to use this function to resolve a separate issue but it had the happy co-incidence of fixing this problem as well.

=sum(aggr(

(sum({$<

Customer,

[Sales Invoice Number],

[Invoice Consignment Line],

[Production Consignment Line] = P([Production Consignment Line]),

$(function.clear_selections(dimension.calendar))

[Production Cost Raw Material Amount])

sum({$<

Customer,

[Sales Invoice Number],

[Invoice Consignment Line],

[Production Consignment Line] = P([Production Consignment Line]),

$(function.clear_selections(dimension.calendar))

[Production Quantity by Raw Material]))

SUM({$<

[Date Type] = {'Delivery Date'},

[Sales Document Type] = {'Sales Invoice'},

[Product Class Id] = {1,7,10}

[Sales Quantity]),

[Production Consignment Line]))