Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

satish25
New Contributor III

Selection from Starting Date to Selected Date

Hello Everyone,

    I want an expression for Sum(Sales) from Starting Date to Selected Date where a user selects Year, Month and Day.

Suppose a Date (MM-DD-YYYY) 1/22/2017 is selected (I have data from 1/1/2010), then the Sum(Sales) should be from Start date (1/1/2010) till 1/22/2017.

The Expression in the Data Load Editor is as Given below:

LOAD

    "Date",

    Month("Date") as Month,

    Year("Date") as Year,

    Day(Date) as "Day1",

    Sales,

    Floor("Date") as DateNum

FROM [lib://Folder/Sales data.xlsx]

(ooxml, embedded labels, table is Sheet1);

I have an expression

if(GetSelectedCount(Date)>0,sum({$ <Date={"<=$(=max(Date))"}> } Sales),Sum({<Date = {">=$(=YearStart(Date))<=$(=Date(Max(Date)))"} >} Sales))


This expression gives the correct result when "Date" field is selected.

I want the correct result when the "Year", "Month" and "Day1" fields are selected not the "Date" field.

Cause in the Main Dashboard, The "Date" filter will not be present. Only the "Year", "Month" and "Day1"  filter will be present



1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Selection from Starting Date to Selected Date

Maybe this:

sum({<Year=,Month=,Day=,Date={"<=$(=max(Date))"}>}YourMeasure)

7 Replies
MVP
MVP

Re: Selection from Starting Date to Selected Date

Luminary
Luminary

Re: Selection from Starting Date to Selected Date

Hi,

to make this work with Year, Month and Day1 fields you need to "turn off" those selections in the set analysis (they will still be used to calculate Max(Date) inside the set analysis).

if(GetSelectedCount(Date)>0,sum({$ <Year, Month, Day1, Date={"<=$(=max(Date))"}> } Sales),Sum({<Year, Month, Day1,Date = {">=$(=YearStart(Date))<=$(=Date(Max(Date)))"} >} Sales))

Hope this helps.

Juraj

satish25
New Contributor III

Re: Selection from Starting Date to Selected Date

Hi Juraj,

Thanks for the reply

The Expression is working for selections on Year and Month

However it doesn't show the correct answer when the Day1 field is selected. (But it does work work when 2 or more Day1 fields are selected. It then shows the value for the MAX Day1 field selected i.e. if Day1 value like 2 and 10 are selected then it shows the value of 10th, but if only 10 is selected then it gives the wrong answer).

Luminary
Luminary

Re: Selection from Starting Date to Selected Date

Can you post a screenshot or an example? That would help me to understand what's going on.

satish25
New Contributor III

Re: Selection from Starting Date to Selected Date

Here is a sample Data.

OmarBenSalem
Esteemed Contributor

Re: Selection from Starting Date to Selected Date

Maybe this:

sum({<Year=,Month=,Day=,Date={"<=$(=max(Date))"}>}YourMeasure)

satish25
New Contributor III

Re: Selection from Starting Date to Selected Date

Thanks a lot

Community Browser