Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Im fairly new to developing in Qlikview (as well as developing in general) and need some expression scripting help. I previously focused on Qlikview Dashboard UI design, but due to some employee shuffling, im being asked to do more data crunching and scripting.
Im trying to build an expression where i have the values "FISCAL_MONTH" , "BOOKINGS_UNITS" and "BOOKING_USD".
what im trying to accomplish is something like this:
BOOKING_USD (from September for example) / BOOKING_UNITS (from October)
how would i make an expression that would draw numbers from the last month, then divide that value by data of the current month?
FISCAL_MONTH data is listed out as 1-12.
I attempted building this expression (again, new at scripting) but id doesnt seem to work.
(sum( { $ < FISCAL_MONTH = { $ (=only(FISCAL_MONTH) -1 ) } > } BOOKING_USD ) ) / sum(BOOKING_UNITS)
any suggestions? am i way off?
Hi
Try max() instead of only
sum({< FISCAL_MONTH = {$(=max(FISCAL_MONTH)-1)}>} BOOKING_USD) / sum(BOOKING_UNITS)
Regards,
Alex
Try
(sum( { $ < FISCAL_MONTH = { $ (=max(FISCAL_MONTH) -1 ) } > } BOOKING_USD ) /
sum( { $ < FISCAL_MONTH = { $ (=max(FISCAL_MONTH) ) } > } BOOKING_USD )
Hope to help!!!
hmm,
again, both posts look like logical statements and makes sense to me, but I'm still getting the same null value. to check my data, i made a sample table of data for both BOOKING_USD and BOOKING_UNITS and the values exist, but cant figure out why this wouldn't work.
Is there a possibility that when calculating the first displayable month, it can't draw the month before, causing a string of null values? (ie: first month recorded is August, no data for July can be found)
Can u plz share ur SAMPLE DATA so that we could help u more efficienty..
Hi Ryan
Try using this,its a very clever tool developed by Stefan Walther
It makes set analysis Childs play
http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=
From what I can see above it could have something to do with the format your date is in
try putting =max(FISCAL_MONTH) -1 into a text object and see what it returns
Hi Ryan,
Try this expression
=sum( { DateField={'>=$(=MonthStart(Max(DateField), -1))<=$(=MonthEnd(Max(DateField), -1))'} > } BOOKING_USD) /
sum( { DateField={'>=$(=MonthStart(Max(DateField)))<=$(=Today())'} > }BOOKING_UNITS )
Hope this helps you.
Regards,
Jagan.
Thank you everyone for your support, i've learned quite a bit more about set analysis and i tried many of your suggestions. i got it working correctly with the test data i was about to upload (using the set analysis wizard, thanks ashkrit501) .
I also recieved a response from my database contact. He double checked the backend and there was an issue that there were gaps in our BOOKING_USD field, which may have caused the null values popping up for our test Product we used when making our Qlikview Dashboard.