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: 
gradiske
Contributor III
Contributor III

how to exclude dates between dates that already exists

Sorry guys, i was trying to edit with my smartphone yesterday and something went wrong =/

1 Solution

Accepted Solutions
Not applicable

Hi there,

Check out the attached file. I created a flag on the back-end and used that in set-analysis for sum. The flag works per your logic.

Hope it helps

Thanks

AJ

View solution in original post

10 Replies
sudeepkm
Specialist III
Specialist III

you can load firstvalue(Period_ID) group by  User_id.

Please take a look at the example.

gradiske
Contributor III
Contributor III
Author

That's a good way to work around but, i have to consider period_id 4 too... since it's not between the period_id 1. And not always the first value it's the most large range between dates...

sudeepkm
Specialist III
Specialist III

oh sorry I completely forgot about it. let me consider the date.

Not applicable

Hi there,

Check out the attached file. I created a flag on the back-end and used that in set-analysis for sum. The flag works per your logic.

Hope it helps

Thanks

AJ

gradiske
Contributor III
Contributor III
Author

You sir, made my day! Thank you so much!

Thank you too, Sudeep! For the effort!

But Ajay, could you explain to me the structure behind that? It will work to me but to be honest, i didn't understand a lot of whats going on in the script, hehe, specially the table with noconcatenate.

Thanks again!

Not applicable

Glad it worked for you.

In situations where you have to compare values with previous record values Peek() or Previous() functions come in handy. Also the table must be sorted using Order by.

Now I created new date fields Start and Final which will essentially replace the actual dates with values from previous record if condition is true. In this case  dates of 2 and 3 lie within 1 so their dates will be replaced with 1's date. This will flag that record saying it shouldn't be included when summing, which is controlled in Set Analysis on the frond-end.

It will sum only if Flag =1.

gradiske
Contributor III
Contributor III
Author

I've used Previous() before, and i actually thought about using it on this situation but i was kinda lost where and when to use it. It seems that everytime i've tried to solve this problem, it would escape more and more from the solution, hehe. Good to know that peek() works for this!


Thank you so much, sir!

MarcoWedel

did you deliberately delete your question?

gradiske
Contributor III
Contributor III
Author

wow! o_O

Yesterday i was trying to edit with my smartphone and was giving me some errors, after a few times that i've tried, i gave up. That's weird!