topic Re: Set Analysis Explanation in QlikView

Set Analysis Explanation

prees959 — Wed, 13 Sep 2017 14:43:30 GMT

Hi.

Can someone explain what is happening in this expression please:

=sum({<$(vCalendar1),Date=P(Date)>}[all_values])

Many Thanks,

Phil

Re: Set Analysis Explanation

sunny_talwar — Wed, 13 Sep 2017 14:51:16 GMT

It seems that you are ignoring all selection in calendar fields within your dashboard and only looking to see possible Dates with this expression

Re: Set Analysis Explanation

prees959 — Wed, 13 Sep 2017 14:52:15 GMT

thank you

Re: Set Analysis Explanation

Anil_Babu_Samineni — Wed, 13 Sep 2017 14:52:39 GMT

It sum the all_values when all possible valuess from Date field and Calendar1 variable. We are not sure, What variable is declare in your environment?

Re: Set Analysis Explanation

sunny_talwar — Wed, 13 Sep 2017 14:54:06 GMT

I think it might be a Concatenated list of fields from the calendar table, but excluding Date field

Re: Set Analysis Explanation

prees959 — Thu, 14 Sep 2017 07:15:19 GMT

Hi,

Yes, $(vCalendar1) seems to be a concatenated list of fields. The above expression isn't working correctly. When I place $(vCalendar1) into a textbox there is an Error: Garbage after expression: ","

The variable is set as [Year],[MonthYear],[Fiscal Month],[Fiscal Yr],[Main Date]

however the expression has a red underline from [Month Year] to [Main Date].

I have checked that all these fields are available in the data model and they are.

Can you help please?

Phil

Re: Set Analysis Explanation

jonathandienst — Thu, 14 Sep 2017 07:22:07 GMT

>>When I place $(vCalendar1) into a textbox there is an Error: Garbage after expression: ","

That's because $(vCalendar1) is trying to evaluate the list as an expression, which will clearly not work. To see this variable in a text box, just use =vCalendar1 as the expression.

>>however the expression has a red underline from [Month Year] to [Main Date].

For the same reason...

Re: Set Analysis Explanation

prees959 — Thu, 14 Sep 2017 07:32:00 GMT

ok,

so should be expression be

=sum({<vCalendar1,Date=P(Date)>}[all_values]) ?

Re: Set Analysis Explanation

sunny_talwar — Thu, 14 Sep 2017 08:13:09 GMT

I think for checking its value in text box you need to just use =vCalendar, but within the set analysis, you might still need dollar sign expansion

=Sum({<$(vCalendar1),Date=P(Date)>}[all_values])

Is this not working?

Re: Set Analysis Explanation

prees959 — Thu, 14 Sep 2017 08:17:24 GMT

Hi Sunny,

There is a value in the text box - but when the a date value is selected - it doesnt change - whereas a simple sum(all_values) does change when different dates are selected....

Re: Set Analysis Explanation

sunny_talwar — Thu, 14 Sep 2017 08:18:59 GMT

What is difference between Date and Main Date? I see your variable uses Main Date within vCalendar? Do you make selection in Main Date or Date?

Re: Set Analysis Explanation

prees959 — Fri, 15 Sep 2017 09:18:45 GMT

sorry, the expression should read :

=sum({<$(vCalendar1),[Main Date]=P(Date)>}[all_values])

vCalendar1 is set to [Year],[MonthYear],[Fiscal Month],[Fiscal Yr],[Main Date]

Date selections are made using the fields used in the variable above. One thing I can see is that Date is in a calendar which isn't joined in my data model.... [Year],[MonthYear],[Fiscal Month],[Fiscal Yr],[Main Date] are all part of a SalesCalendar which is connected in the data model....

Phil

Re: Set Analysis Explanation

sunny_talwar — Fri, 15 Sep 2017 11:30:38 GMT

Make sure that vCalendar1 is without Main Date because you cannot have 2 set analysis on the same field.... If you go have that only the first one is honored... so in your case because [Main Date] comes before [Main Date] = p(Date), the first one work, second one doesn't. A work around would be to use this

=Sum({<[Main Date]=P(Date), $(vCalendar1)>}[all_values])