How to take all dates including Previous Dates when selecting a date in qlikview

shabarish0587 — Wed, 25 Nov 2020 16:16:04 GMT

Hello Everyone,

I want an expression for Count(IDs) from Starting Date to Selected Date where a user selects Year or YearMonth .

Suppose a YearMonth (YYYY-MM) 2018-10 is selected in line chart I need to display data for weekly. (I have data from 1/1/2010), then the count(Ids) should be from Start date (1/1/2010) till 10/31/2018 until selected Month.

So for example If I select YearMonth is 2018-10, for that we have five weeks. Here I am defining Weekend as Saturday.

In line charts Weeks are displaying like 2018-10-06, 2018-10-13, 2018-10-20, 2018-10-27, 2018-11-03

Here I am using this below expression:

num(count(DISTINCT if(((IsNull(END_Date) and Open_Date<='2018-10-06') or (END_Date>'2018-10-06 'and Open_Date<='2018-10-06')), IDs)),'##,##')

the above expression I am taking hard code for one week. it is showing correct value. because we are using hard coding. but when we are using field . it Is not showing correct out put. why because , if I want to see the week data for 2018-10-06. the output for that week is 239 by using hard coding. Instead of hard-coding value is showing 66 means it is taking data from 2018-10-01 to 2018-10-06. That is why it is showing wrong out. But I want to take data if I select 2018-10-06. it should take data for open_date <=2018-10-06(till 01/01/2010) . for End_date>2018-10-06(till 2018-10-31)

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

LOAD

"Date",

Month("Date") as Month,

Year_Month,

Year("Date") as Year,

Day(Date) as "Day1",

Ids,

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

(ooxml, embedded labels, table is Sheet1);

Can any one help me on this.

Re: How to take all dates including Previous Dates when selecting a date in qlikview

cbushey1 — Tue, 09 Apr 2019 13:02:32 GMT

What if you put this into set analysis. So something like this:

Count(distinct {<OPEN_date = ">date", END_date = "<DateSelected">} ID)

Where date represents either a hard-coded start date (if it is always the same), DateSelected is the value of the date the user is selecting.

topic Re: How to take all dates including Previous Dates when selecting a date in qlikview in QlikView

How to take all dates including Previous Dates when selecting a date in qlikview

Re: How to take all dates including Previous Dates when selecting a date in qlikview