Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Help with set analysis "Date range"

I need to create a report to show the different between the "total number of Sales" and "total number of sales the customer participate".

My Format is :

  • For the total number of sales within date range: =Count({1<[Status ID]= {3,4}, [Type ID] = {1}, [Sales Date] = {">=$(=Date($(vFromDate)))<=$(=Date($(vToDate)))"}>} DISTINCT [Sales ID])
  • For the total of sales selected customer participate: =Count({$<[Status ID]= {3,4}, [Type ID] = {1}, [Run Date] = {">=$(=Date($(vFromDate)))<=$(=Date($(vToDate)))"} >} DISTINCT [Sales ID])

but both of them are not working....

Can someone help me on this???

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Help with set analysis "Date range"

Many thanks to Jagan and Rajkumar. I make some changes to my formula.

I found that my default format for [Sales Date] is DD/MM/YYYY so I changed the Date function to match this format:

  • For the total number of sales within date range: =Count({1<[Status ID]= {3,4}, [Type ID] = {1}, [Sales Date] = {">=$(=Date(vFromDate, 'DD/MM/YYYY'))<=$(=Date(vToDate, 'DD/MM/YYYY'))"}>} DISTINCT [Sales ID])
  • For the total of sales selected customer participate: =Count({$<[Status ID]= {3,4}, [Type ID] = {1}, [Run Date] = {">=$(=Date(vFromDate, 'DD/MM/YYYY'))<=$(=Date(vToDate, 'DD/MM/YYYY'))"} >} DISTINCT [Sales ID])

View solution in original post

7 Replies
Highlighted
Not applicable

Re: Help with set analysis "Date range"

Hi,

For showing total sales irrespective of customer try this expression

Count({<[Status ID]= {3,4}, [Type ID] = {1}, [Sales Date] = {">=$(=Date($(vFromDate)))<=$(=Date($(vToDate)))"},Customer=>} DISTINCT [Sales ID])

Or Post your sample file

Highlighted
Not applicable

Re: Help with set analysis "Date range"

I use Count({1}. So all the selections will be ignore.

The problem should be caused by the Date range. I remove the date range part it is working...

Highlighted
Partner
Partner

Re: Help with set analysis "Date range"

Try to use numeric date. Set analysis does not handle well date format.

Create a "Sales_date_num" in your script (by multiplying your "Sales Date" by 1).

[Sales Date] * 1 as Sales_date_num

Be sure your variable use also numeric format.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Help with set analysis "Date range"

Hi,

Check this expression

=Count({1<[Status ID]= {3,4}, [Type ID] = {1}, [Sales Date] = {">=$(=Date(vFromDate))<=$(=Date(vToDate))"}>} DISTINCT [Sales ID])

If not working then check whether the format of dates are same, if not convert to the same format by using Date(date, formatspecifier).

check [Sales Date] and =Date((vFromDate))

Hope this helps you.

Regards,

Jagan.

Highlighted
Not applicable

Re: Help with set analysis "Date range"

If date formats of [Sales Date], vFromDate and  vToDate are same (e.g."03/01/2011 13:12")

Then use date range {">=$(=vFromDate)<=$(=vToDate)"}.

If date formats of [Sales Date],vFromDate and vToDate are not same (e.g."vToDate &vFromDate are like =03/01/2011 13:12" and [Sales Date] like "03/01/2011')

Then use date range {">=$(=Date(vFromDate))<=$(=Date(vToDate))"}

Or let me know your Date format then I can provide you proper code.

Highlighted
Not applicable

Re: Help with set analysis "Date range"

Thank you very much. You are right, Set analysis does not handle well the date format. But for my case, change of the load script is not a very good idea. I have far too many tables and other reports......

But this is still a good idea. Maybe I can try this next time~~

Highlighted
Not applicable

Re: Help with set analysis "Date range"

Many thanks to Jagan and Rajkumar. I make some changes to my formula.

I found that my default format for [Sales Date] is DD/MM/YYYY so I changed the Date function to match this format:

  • For the total number of sales within date range: =Count({1<[Status ID]= {3,4}, [Type ID] = {1}, [Sales Date] = {">=$(=Date(vFromDate, 'DD/MM/YYYY'))<=$(=Date(vToDate, 'DD/MM/YYYY'))"}>} DISTINCT [Sales ID])
  • For the total of sales selected customer participate: =Count({$<[Status ID]= {3,4}, [Type ID] = {1}, [Run Date] = {">=$(=Date(vFromDate, 'DD/MM/YYYY'))<=$(=Date(vToDate, 'DD/MM/YYYY'))"} >} DISTINCT [Sales ID])

View solution in original post