Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
satish25
Contributor III
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
Partner
Partner

Maybe this:

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

View solution in original post

11 Replies
MK_QSL
MVP
MVP

juraj_misina
Partner
Partner

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
Contributor III
Contributor III
Author

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

juraj_misina
Partner
Partner

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

satish25
Contributor III
Contributor III
Author

Here is a sample Data.

OmarBenSalem
Partner
Partner

Maybe this:

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

satish25
Contributor III
Contributor III
Author

Thanks a lot

beskqlik
Contributor II
Contributor II

HI

by seeing your post, i thought as you can help you on my issue.

I have one date column at filter panel. when select i date at filter i should get all the previous 90 dates to selected date and also need to calculate another bucketing column as 0 -30,31-60,61-90  as for for last 30 days as 0-30 and next as 31-60 and 61-90.

Can you please help

beskqlik
Contributor II
Contributor II

Please find my requirment indetail with sample

Have  date column as below

date
3/20/2019
3/21/2019
3/22/2019
3/23/2019
3/24/2019
3/25/2019
3/26/2019
3/27/2019
3/28/2019
3/29/2019
3/30/2019
3/31/2019
4/1/2019
4/2/2019
4/3/2019
4/4/2019
4/5/2019
4/6/2019
4/7/2019

Need result as below

At Filter Panel selected date 4/2/2019

We should get last 10 days from selected dates as below                   Need bucket as below
3/24/2019                                                                                                                 greater than 6 days
3/25/2019                                                                                                                  greater than 6 days
3/26/2019                                                                                                                 greater than 6 days
3/27/2019                                                                                                                 greater than 6 days
3/28/2019                                                                                                                  4-6 days
3/29/2019                                                                                                                  4-6 days
3/30/2019                                                                                                                    4-6 days
3/31/2019                                                                                                                   1-3 days
4/1/2019                                                                                                                     1-3 days
4/2/2019                                                                                                                       1-3 days