topic Re: Set Analysis Formula ? newbie help in QlikView

Set Analysis Formula ? newbie help

Thu, 11 Oct 2012 23:50:34 GMT

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?

Re: Set Analysis Formula ? newbie help

Fri, 12 Oct 2012 01:58:26 GMT

Try max() instead of only

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

Regards,

Alex

Re: Set Analysis Formula ? newbie help

rajni_batra — Fri, 12 Oct 2012 06:05:30 GMT

Try

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

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

Hope to help!!!

Re: Set Analysis Formula ? newbie help

Mon, 15 Oct 2012 16:25:58 GMT

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)

Re: Set Analysis Formula ? newbie help

rajni_batra — Tue, 16 Oct 2012 04:49:49 GMT

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

Re: Set Analysis Formula ? newbie help

ashkrit501 — Tue, 16 Oct 2012 05:04:29 GMT

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

Re: Set Analysis Formula ? newbie help

jagan — Tue, 16 Oct 2012 06:04:34 GMT

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.

Re: Set Analysis Formula ? newbie help

Mon, 22 Oct 2012 22:57:50 GMT

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.