Newbie Set Analysis problem

[#1]

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

[#2]

Hi Cam:

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

Regards,

+Rubén

[mikecrengland · Accepted solution]

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

Mike

[#4]

OK - doing that the values come back as 0.

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

[#5]

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

[maxgro]

as Mike suggested, around $(vDate_Today)

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

[#7]

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.

[#8]

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.