topic Re: Evaluating Expression on one field selection in Qlik Automate

Evaluating Expression on one field selection

ferha_jafri — Tue, 14 Mar 2023 13:48:53 GMT

Hello Experts,

I have a requirement, where I have data as Date1, Date2,Sales. In front end I have filter as Date1 but I want if user select Date1 dates, sum of sales get evaluated for date2 = date selected date 1, P function I already tried and this is not working in this scenario as I want to calculate sum({<Date1 selected values = Date2 values>}sale).

Thanks in advance

Ferha

Re: Evaluating Expression on one field selection

marcus_sommer — Tue, 14 Mar 2023 16:31:45 GMT

You mean something like?

sum({< FactCalendarDate = p(IslandDate)>} Sales)

Re: Evaluating Expression on one field selection

ferha_jafri — Tue, 14 Mar 2023 16:46:13 GMT

I thought it will work in that way but it is not giving the desired result. As an eg. if Date1 is 1 Nov 2022, Date2 is 2 Dec 2022, Sale is 200

Date1 is 2 Nov 2022, Date2 is 1 Nov 2022, Sale is 500

In front if I select Date1 as 1 Nov 2022 then sum of sales should come out as 500 not 200.

Hope it helps.

Re: Evaluating Expression on one field selection

marcus_sommer — Wed, 15 Mar 2023 08:33:50 GMT

You need to consider which kind of analysis you for which view want - and what this means to the kind of data-model and UI navigation.

For me it looked as if you have just one date-field and you want to display results between one field-value and another field-value. The simplest way to get such view is just to select all (date) values and the expression would be a simple: sum(Sales). In many scenarios this is quite simple and needs just a few clicks - at first on relating year/quarter/month which reduced the amount of possible dates and (usually) adjust the list-box orders. Also possible and not much difficult would be just to use a search on the field with something:

>=date-value1<=date-value2

Beside this a lot of the often queried YTD, MTD, ... the ones from the previous years and many more selections could be simplified with appropriate flags within the master-calendar and/or with an as-of-table.

This means the use of multiple date-fields is not mandatory necessary. Nevertheless is such usability quite common and often implemented per extra variables or like above hinted with date-fields from island-tables. And only with this it would be possible to query independent selections and/or to implement a >= <= logic between them. IMO you should have very good reasons to go in this direction because the above shown selection-approach is more natively and also easier within the development as well as within the user-experience.