topic Re: Last Seven days count not worked properly in QlikView

Last Seven days count not worked properly

gopinadhpindra — Fri, 12 Oct 2018 07:11:15 GMT

Hi Qlik Experts,

=count({<Opened_date ={">=$(date(max(Opened_date)-7))<=$(date(max(Opened_date)))"},State={'Resolved'}>}Number)

The above expression showing total Resolved count but i need to show it as last 7 days count

please post your suggestions

Thanks

Gopinadh

Re: Last Seven days count not worked properly

dplr-rn — Fri, 12 Oct 2018 17:28:47 GMT

try

=count({<Opened_date ={">=$(=date(max(Opened_date)-7))<=$(=date(max(Opened_date)))"},State={'Resolved'}>}Number)

Re: Last Seven days count not worked properly

gopinadhpindra — Sat, 13 Oct 2018 09:34:06 GMT

@Dilip Thanks for your help but its not work properly

actually the original date was in timestamp format and after i was converted into date format date(floor(Opened),'DD/MM/YYYY') as opened_date

might be this has been the issue or not please correct me

data is like this

Number	Priority	State	Opened	Closed	Due date
INC123	4 - Low	Open	2018-10-10 10:43:29	2018-10-10 10:43:29
INC124	4 - Low	Work in Progress	2018-10-10 10:21:26	2018-10-13 10:21:26
INC125	4 - Low	Work in Progress	2018-10-10 10:18:59	2018-10-13 10:18:59
INC126	4 - Low	Work in Progress	2018-10-10 10:16:01	2018-10-13 10:16:01
INC127	4 - Low	Open	2018-10-10 10:14:56	2018-10-13 10:14:56
INC128	4 - Low	Work in Progress	2018-10-10 10:08:41	2018-10-15 10:08:41

Re: Last Seven days count not worked properly

dplr-rn — Sat, 13 Oct 2018 13:18:05 GMT

Timestamp can cause issues. And should be removed or moved to a separate column

Re: Last Seven days count not worked properly

gopinadhpindra — Sat, 13 Oct 2018 13:36:09 GMT

already converted into the date format date(floor(Opened),'DD/MM/YYYY') as opened_date

we can't change the master data

Any Suggestions Plz????

Re: Last Seven days count not worked properly

gopinadhpindra — Sat, 13 Oct 2018 17:27:26 GMT

=count({<NEWDATE={"$(=Max(NEWDATE,7))"},State={'Resolved'}>}Number)

+count({<NEWDATE={"$(=Max(NEWDATE,6))"},State={'Resolved'}>}Number)

+count({<NEWDATE={"$(=Max(NEWDATE,5))"},State={'Resolved'}>}Number)

+count({<NEWDATE={"$(=Max(NEWDATE,4))"},State={'Resolved'}>}Number)

+count({<NEWDATE={"$(=Max(NEWDATE,3))"},State={'Resolved'}>}Number)

+count({<NEWDATE={"$(=Max(NEWDATE,2))"},State={'Resolved'}>}Number)

+count({<NEWDATE={"$(=Max(NEWDATE,1))"},State={'Resolved'}>}Number)

this exp is working fine is there any other way to make it short

Re: Last Seven days count not worked properly

MarcoWedel — Sun, 14 Oct 2018 21:22:06 GMT

Hi,

pay attention to your field name cases and date formats:

Your expression uses a field "Opened_date" (upper case "O") while your load script generates a field "opened_date" (lower case "o").

One possible solution might be:

=Count({$<opened_date ={">$(=Date(Max(opened_date)-7,'DD/MM/YYYY'))<=$(=Date(Max(opened_date),'DD/MM/YYYY'))"},State={'Resolved'}>}Number)

hope this helps

regards

Marco

Re: Last Seven days count not worked properly

gopinadhpindra — Tue, 16 Oct 2018 05:31:01 GMT

Thanks for your reply marco

Still not working its showing zero

Re: Last Seven days count not worked properly

Anil_Babu_Samineni — Tue, 16 Oct 2018 07:14:01 GMT

Could be format Issue? Or can you show using this below in text object and Show image?

='>=' & Date(max(Opened_date-7)) & '<=' & date(max(Opened_date))

Re: Last Seven days count not worked properly

MarcoWedel — Tue, 16 Oct 2018 18:15:15 GMT

seems to work for me:

hope this helps

regards

Marco

Re: Last Seven days count not worked properly

Zhandos_Shotan — Tue, 16 Oct 2018 18:57:36 GMT

Hi!

Had same troubles with timestamps.

I found that the reason is Date() acually doesnt cut date part from timestamp. It makes date representation but Dual field with hidden time part as sorting value.

I used

Date(Subfield(Opened,' ',1)) as opened_date

to get proper date field.

Hope this helps, and take a note Dilip Ranjith's 1st reply.

Re: Last Seven days count not worked properly

gopinadhpindra — Wed, 24 Oct 2018 07:05:29 GMT

its working fine clear selections mode in Qliksene text object but while making any selections in state field means its doesn't work its not taking that last seven days intervel its seems

=Count({1<Opened_date={"=Opened_date>=Today()-7"},State={'Resolved','Closed'}>}Number)

in this expression i want last seven days total count irrespective of selection like date=,priority=,state=,etc like that please correct me where i can make a mistake

Thanks

Re: Last Seven days count not worked properly

gopinadhpindra — Wed, 24 Oct 2018 07:26:46 GMT

Yes anil,date has been in timestamp while converting into this date format and still its showing number format only in qliksense Ex: =date(today())

and Qlikview its working fine why in qliksense taking number format

its working fine clear selections mode in Qliksene text object but while making any selections in state field means its doesn't work its not taking that last seven days intervel its seems

=Count({1<Opened_date={"=Opened_date>=Today()-7"},State={'Resolved','Closed'}>}Number)

in this expression i want last seven days total count irrespective of selection like date=,priority=,state=,etc like that please correct me where i can make a mistake

Thanks

Re: Last Seven days count not worked properly

gopinadhpindra — Wed, 24 Oct 2018 07:29:18 GMT

Thanks for the reply ranjith i will check and let you know for this

Re: Last Seven days count not worked properly

gopinadhpindra — Thu, 25 Oct 2018 12:38:23 GMT

it has been picked the date but while making the selection its varying like existing one only

Re: Last Seven days count not worked properly

MarcoWedel — Thu, 25 Oct 2018 20:54:17 GMT

please post a sample application to demonstrate this issue.

Re: Last Seven days count not worked properly

gopinadhpindra — Fri, 26 Oct 2018 07:48:26 GMT

Hi Marco,

Thanks for your time Marco please find the attached app in your meantime

while making the state selections its varying,i want that object into static state(Irrespective of selections)

If you want any information needed i will provide you

Thanks & Regards,

Gopinadh

Re: Last Seven days count not worked properly

gopinadhpindra — Fri, 26 Oct 2018 08:11:21 GMT

Hi Marco,

Please find the data as well

Thanks

Gopinadh

Re: Last Seven days count not worked properly

nasirsaikh — Sat, 27 Oct 2018 10:57:06 GMT

=Count({1<Opened_date={"=Opened_date>=Today()-7"},State={'Resolved','Closed'}>}Number)

Re: Last Seven days count not worked properly

gopinadhpindra — Sat, 27 Oct 2018 11:47:17 GMT

Hi Nasir,

please find my application,the problem which was while making the state selections its varying,i want that object into static state(Irrespective of selections)

Thanks