Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stig1984
Creator II
Creator II

Changing date values in expressions

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)

Capture.PNG

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.

6 Replies
sunny_talwar

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

stig1984
Creator II
Creator II
Author

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.

sunny_talwar

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

stig1984
Creator II
Creator II
Author

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.

stig1984
Creator II
Creator II
Author

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]))