# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean:
Creator

## Date Range Selection and Sum of av. Sales

hey

have a table which is shows inventory, sales and prodcuction issues by item and date.

Date (daily update) | ItemNo | InventoryOnHandQTY | ProductionIssueQTY | SalesIssueQTY

Now i would like to evaluate the a rolling average consumption by month (productionIssue + SalesIssues). The average should be calculated based on the past 12 months.

Additionally i like to go back to a specific date and see what the stock and average consumption was.

What i could find here in the forum is how to calculate the av. consumption

1. I made a variable vInventoryMaxDate

=max([Date process])

2. I made a straight table dimension ItemNo and expression for the InventoryOnHand and one for the calculation of the av. consumption

Sum ( {<Date ={">=\$(=vInventoryMaxDate-365)<\$(=vInventoryMaxDate)"}>} (#ProductionIssue + #SalesIssue)/12)

Eveerything is working fine and the figures are correct, as long im not going to select any date. If i select a date, just that specific day will be calculated as consumption, even my date range is 365 days.

How can i make that the consumption expression will ignore the date selection and go back to pick all the figures?

If i set the identifier to 1, then the item selection will not work anylonger...

Sum (1{<Date ={">=\$(=vInventoryMaxDate-365)<\$(=vInventoryMaxDate)"}>} (#ProductionIssue + #SalesIssue)/12)

cheers Rob

1 Solution

Accepted Solutions
MVP

First of all you are the wrong placement of 1. You need it be between { and <

Sum ({1<Date ={">=\$(=vInventoryMaxDate-365)<\$(=vInventoryMaxDate)"}>} (#ProductionIssue + #SalesIssue)/12)

Second, are you making selection in Date field or another calendar field? If you are making in another field, you can add it to your set analysis so that it ignores any selection made in other calendar field:

Sum ({<Date ={">=\$(=vInventoryMaxDate-365)<\$(=vInventoryMaxDate)"}, Year, Month, Day, MonthYear, Quarter, QuarterYear>} (#ProductionIssue + #SalesIssue)/12)

5 Replies
MVP

First of all you are the wrong placement of 1. You need it be between { and <

Sum ({1<Date ={">=\$(=vInventoryMaxDate-365)<\$(=vInventoryMaxDate)"}>} (#ProductionIssue + #SalesIssue)/12)

Second, are you making selection in Date field or another calendar field? If you are making in another field, you can add it to your set analysis so that it ignores any selection made in other calendar field:

Sum ({<Date ={">=\$(=vInventoryMaxDate-365)<\$(=vInventoryMaxDate)"}, Year, Month, Day, MonthYear, Quarter, QuarterYear>} (#ProductionIssue + #SalesIssue)/12)

Creator
Author

Hi Sunny

Thansk first for support

Yes, sorry forgot about that, the data is linked with the Date field to the calendar table.

how should the second expression look like? Do i need to enter all fields from the calendar table, or just one value? Did try just to pick en unspecific value, see expresion below... It doesnt work

Sum ( {<[Date process] ={">=\$(=vInventoryMaxDate-365)<\$(=vInventoryMaxDate)"}, [Month process] ={'*'}>} (#ProductionIssue + #SalesIssue)/12)

many thanks

MVP

Wherever in the calendar table the user can make a selection in your application add that to your set analysis for the expression.

Creator
Author

Ok, need just to add the modifier without any condition Thats easy... Now it works

Sum ( {<[Date process] ={">=\$(=vInventoryMaxDate-365)<\$(=vInventoryMaxDate)"}, %QuarterProcess, [Year process], [Month process],[Week process] >} (#ProductionIssue + #SalesIssue)/12)

[Month process] ={'*'}

correct is like this

[Month process],

Cool, thanks for you help

MVP

Yup, not a problem

Community Browser