Filter alternative states

[domm]

Hi,

Is it possible to have  3 tables fixed on 3 different alternative states and filter all of  them by dates at the same time (with the same data picker) ?

Thanks,

Domm

[OmarBenSalem]

No.

Not with alternate state, each object could only be associated to one alternate state.

If u want to achieve such a thing, u should use set expressions.

[domm]

Thanks, so do I have to create different measures like that one

sum({<  {Market] = {'DE'}, [Observation] = {">=$(=min([CalendarDate_Observation]))<=$(=max([CalendarDate_Observation]))"} >} Revenue)  ?

Or there is another way to fix it?

[rwunderlich · Accepted solution]

Yes you can.  A set expression can refer to a field in another state using the syntax:

statename::field

For example:

Sum({<Year=$::Year>}Sales)

will use the Year value(s) from the default state regardless of what state the current object is in.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[OmarBenSalem]

Finally; someone explained the meaning of the $:: syntax ! 

Thanks !!

[domm]

Thanks for your answer.

I tried to do it, but I doesn't work. I don't why QlikSense doesn't know my state

I created it from "Alternative states" --> "Create new"

How can I fix this problem?

[OmarBenSalem]

Please re-read Rob's answer.

That's not what he meant...

You have State1 and want ur chart to still be modified when u filter with a date field (not related to the state)

u do:

sum({<date=$::date>}measure)

[domm]

Thanks, now it works. I was confused about statename::field.

Thanks 🙂

[domm]

Hi,

your solution works for sum({< Observation= $::CalendarDate_Observation>}  Revenue)

I also have to calculate the Monthly Goal like

(if((Interval( (date((max([CalendarDate_Observation])),'DD-MMM-YYYY')) - (date(min([CalendarDate_Observation]),'DD-MMM-YYYY')), 'd') ) < 30, 

 Sum({< [Observation] ={">=$(=monthStart(max([CalendarDate_Observation]))) <=$(=monthEnd(max([CalendarDate_Observation])))"}, [KPI] = {"Rev"} >}  Target),
 
  Sum({<  [Observation] ={">=$(=monthStart(min([CalendarDate_Observation]))) <=$(=monthEnd(max([CalendarDate_Observation]))) "}, [ [KPI] = {"Rev"} >} Target))
)

How can I re-write it using alternative states?

About the sum I tried

sum({< CalendarDate_Observation= $::CalendarDate_Observation, g.Observation ={">=$(=monthStart(max([CalendarDate_Observation]))) <=$(=monthEnd(max([CalendarDate_Observation])))"}, [t.KPI] = {"IHC_Rev"}>}  Target)

The result is not what I want, because max([CalendarDate_Observation]) is always January 2019.

How can I get the current monthStart(max([CalendarDate_Observation]))?

Can you help me with the syntax, please?

Thanks in advance,

Domm

[rwunderlich]

You can use set expressions inside any aggregation function.  So use it in the max function as well. 

-Rob