Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gopinadhpindra
Contributor II
Contributor II

Last Seven days count not worked properly

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

19 Replies
dplr-rn
Partner - Master III
Partner - Master III

try

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

gopinadhpindra
Contributor II
Contributor II
Author

@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

  

NumberPriorityStateOpenedClosedDue date
INC1234 - LowOpen2018-10-10 10:43:292018-10-10 10:43:29
INC1244 - LowWork in Progress2018-10-10 10:21:262018-10-13 10:21:26
INC1254 - LowWork in Progress2018-10-10 10:18:592018-10-13 10:18:59
INC1264 - LowWork in Progress2018-10-10 10:16:012018-10-13 10:16:01
INC1274 - LowOpen2018-10-10 10:14:562018-10-13 10:14:56
INC1284 - LowWork in Progress2018-10-10 10:08:412018-10-15 10:08:41
dplr-rn
Partner - Master III
Partner - Master III

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

gopinadhpindra
Contributor II
Contributor II
Author

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????

gopinadhpindra
Contributor II
Contributor II
Author

=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

MarcoWedel

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

gopinadhpindra
Contributor II
Contributor II
Author

Thanks for your reply marco

Still not working its showing zero

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MarcoWedel

seems to work for me:

QlikCommunity_Thread_316679_Pic1.JPG

hope this helps

regards

Marco