Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Tags (1)
1 Reply
Highlighted
Digital Support
Digital Support

Re: calculation of DSO (day sales outstanding) per month

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.