Set Analysis Formula ? newbie help

[#1]

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?

[#2]

Hi

Try max() instead of only

sum({< FISCAL_MONTH = {$(=max(FISCAL_MONTH)-1)}>} BOOKING_USD)  / sum(BOOKING_UNITS)

Regards,

Alex

[rajni_batra]

Try

(sum( { $ < FISCAL_MONTH = { $ (=max(FISCAL_MONTH) -1 ) } > } BOOKING_USD )   /

sum( { $ < FISCAL_MONTH = { $ (=max(FISCAL_MONTH) ) } > } BOOKING_USD )

Hope to help!!!

[#4]

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)

[rajni_batra]

Can u plz share ur SAMPLE DATA so that we could help u more efficienty..

[ashkrit501]

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

[jagan]

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.

[#8]

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.