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: 
YAMAMO
Contributor
Contributor

calculation of DSO (day sales outstanding) per month

Dears ,

Hope you are doing fine! I tried to log this post I did not succeed to do it.

I will be very thankful if you could help me on this difficult calculation, The idea is to calculate the the number of days DSO per month (for every month).

Once we select a month/year, for example if we select Sep 17, we should be able to get the following result (third line) per month :

 

DSO.PNG

 

Right now this in excel with the following logic, for example :

Sep 17: DSO = IF(O4-O3<0;O4/O3*30;IF(O4-O3-N3<0;(O4-O3)/N3*30+30;IF(O4-O3-N3-M3<0;(O4-O3-N3)/M3*30+60;IF(O4-O3-N3-M3-L3<0;(O4-O3-N3-M3)/L3*30+90;IF(O4-O3-N3-M3-L3-K3<0;(O4-O3-N3-M3-L3)/K3*30+120;IF(O4-O3-N3-M3-L3-K3-J3<0;(O4-O3-N3-M3-L3-K3)/J3*30+150;"> 180 Days"))))))

 

Aug-17 : DSO =IF(N4-N3<0;N4/N3*30;IF(N4-N3-M3<0;(N4-N3)/M3*30+30;IF(N4-N3-M3-L3<0;(N4-N3-M3)/L3*30+60;IF(N4-N3-M3-L3-K3<0;(N4-N3-M3-L3)/K3*30+90;IF(N4-N3-M3-L3-K3-J3<0;(N4-N3-M3-L3-K3)/J3*30+120;IF(N4-N3-M3-L3-K3-J3-I3<0;(N4-N3-M3-L3-K3-J3)/I3*30+150;"> 180 Days"))))))

....

Could you please help me translate this into a QV expression/variables.

Many Thanks

1 Reply
Brett_Bleess
Former Employee
Former Employee

The best way to get help with these situations is to attach a QVW file with your data model and some data that reflects what it is you are trying to solve, does not have to be actual, can be sample, but the data model should reflect your total model to ensure you get good responses.  Trying to provide assistance with what you provided is rather dicey which is likely why nobody has responded at this point.  If you have not figured things out, the only place I can point you that will likely be of some help is the Design Blog area, there are hundreds of how-to posts from our experts in this area, so you may be able to find something there to assist you in developing your expressions, but I suspect you may need to use some Set Analysis on this one potentially and possibly use the AGGR function too.  

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

If you can attach an example QVW with some data etc., that should be helpful in getting further responses.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.