topic Re: Newbie Set Analysis problem in QlikView

Newbie Set Analysis problem

Mon, 23 Feb 2015 22:44:39 GMT

I am fairly new, but very comfortable using QV.

I am preparing a dashboard for sales. I am having a couple of issues with Set Analysis - function editor says syntax is OK, but my result throw null or an error.

I am trying to use this in 2 places. 1) a text box displaying a final total and 2) a Pivot Table showing Sales by Salesperson for that day.

I want to avoid having to select the Date_InvoiceDate and SalesType from a selection list

The Set statement:

sum({1 < SalesType={'SLS'}, Date_InvoiceDate = { $(vDate_Today) } >} (Invoice_SalesAmount*Percent/100) )

where vDate_Today = Date(vToday) as a variable

In the text box this throws : Error in set modifier ad hoc element list: ',' or ')' expected
In my Pivot table, it just shows up as a dash

My Dimensions in the pivot table are:

Salesperson, Customer, Vendor, OrderNo

Re: Newbie Set Analysis problem

Mon, 23 Feb 2015 22:54:05 GMT

Hi Cam:

Can you post your model to have a look at it? I'm not sure I understand your problem.

Regards,

+Rubén

Re: Newbie Set Analysis problem

mikecrengland — Mon, 23 Feb 2015 22:56:37 GMT

Unless it returns an integer, you'll need single quotes around $(vDate_Today) .

Mike

Re: Newbie Set Analysis problem

Mon, 23 Feb 2015 23:20:42 GMT

OK - doing that the values come back as 0.

So it must not be getting a good comparison....

Re: Newbie Set Analysis problem

Mon, 23 Feb 2015 23:30:19 GMT

Could you check is $(vDate_Today) returning a date/number? If yes use date($(vDate_Today),'Your invoice datetype')

Re: Newbie Set Analysis problem

maxgro — Mon, 23 Feb 2015 23:34:05 GMT

as Mike suggested, around $(vDate_Today)

sum({$< SalesType={'SLS'}, Date_InvoiceDate = {'$(vDate_Today)' } >} (Invoice_SalesAmount*Percent/100) )

Re: Newbie Set Analysis problem

Mon, 23 Feb 2015 23:44:23 GMT

Sorry - put it around the variable and not the whole expression. Doing this works in the Text box now, but still not in my grid.

Re: Newbie Set Analysis problem

Tue, 24 Feb 2015 00:23:13 GMT

Not sure what is going on but now the values are there. At first, the detail had all zeroes, but subtotals were right Now detail is there.