Hi Folks,
I'm quite new to Qliksense and thought of taking some help from you guys.
Goal:
I have 2 excel formulae which I want to derive in Qliksense. I created measures and got the values for every order. Also, to cross-check I referred a few examples from my excel data sheet to verify those records and they're are correct.
Problem:
Now the issue is when I try to get these values on a monthly level then my values show blank in Qliksense and it only shows Revenue per month, while it shows the below two measures as blank. If I just do Revenue*Interval then I can get those values but what I want is to consider only the business days and also check for a few conditions before I do those calculations.
I only see values for the below columns:
for eg.
EndMonth,Revenue,Revenue*Interval, IntervalAll
2019-June,$100,-,-
2019-July,$100,-,-
And I want to be able to see:
EndMonth,Revenue,Revenue*Interval, IntervalAll on a monthly level.
Formulae:
INTERVAL=NUMBER OF DAYS BETWEEN START AND END DATE
- IF(IF((isnum(Revenue) and Revenue>=0 and (isnum(INTERVAL))),'YES','NO')='NO','',IF(alt(REVENUE*INTERVAL),0,REVENUE*INTERVAL)) as "Revenue*Interval"
- IF(ISNUM(INTERVAL),INTERVAL,IF(isnum(START_DATE),IF(NETWORKDAYS(START_DATE,END_DATE,[holiday])-1<0,0,NETWORKDAYS(START_DATE,END_DATE,[Holiday])-1),'')) as "IntervalAll"
I have attached a sample set of data with the calculations I want. I may be missing something while deriving these values in Qliksense, maybe an aggr() but not too sure.
Any help will be greatly appreciated. Thank you!
