Did you know..? Cyclic Group

[robert_mika]

Working with Qlikview or QlikSense is like playing a good RPG or FPP game - you are working hard looking for  a solution and then you

picking a "Magic Weapon" and you are on next level.

"Did you know...?" is all about quick tips and tricks

So let me start.

Did you know that...

Can the cyclic group be used in an expression?

Did you?

That's good but if you did not - you may be surprised.

Recently I had to find a way to pick up values from Cyclic Group only to discover that after

if(GerCurrentField(...

Qlikview throws beautiful Red Lines.

Head down...fists tight. but wait.

What is the Community for?

My quest leads me to this topic

where our Superhero johnw posted the solution to my problem.

Because QV does not recognize its own syntax you are left out without IntelliSense.

Try to enter long expression - and you will be sweating and swearing...

The good point is - Its Work.

For those who are using personal edition:(with all credits to John) - please try yourself

Script:

dimension:

LOAD * INLINE [

    CategoryID, CategoryName

    1, Car

    2, CD

    3, DVD

    4, Laptop

    5, Software

    6, Sunglass

];

fact_sales:

LOAD * INLINE [

    CategoryID, SaleValue

    1, 1000

    2, 40

    3, 50

    4, 200

    5, 25

    6, 15

];

fact_cost:

LOAD * INLINE [

    CategoryID, CostValue

    1, 700

    2, 30

    3, 25

    4, 120

    5, 5

    6, 8

];

[Expression Group]:

LOAD * INLINE [

Sales, Cost

Sales, Cost

];

GUI:

1)

          Create new bar chart

2)

    Add dimensions:

  a) CategoryName

and

        b)Create new Cyclic dimension SalesCost with Cost and Sales as Dimensions.

3) Add Expression:

=if(getcurrentfield(SalesCost)='Sales',sum(SaleValue),sum(CostValue))

And Voila! you got your Magic Sword.

Good luck in your quests.

Feeling Qlikngry?

How To /Missing Manual(25 articles)

[marcus_malinow]

Nice article!

I have a suggestion however. If you rename your SaleValue field to SalesValue, then this expression will work. This may be slightly better as if you add additional value fields, it will make it simpler to add them in to your expressions. This is similar to a technique I have used in the past to display values in different currencies.

sum($(=GetCurrentField(CostSales)&'Value'))

[sacosta5]

Thanks...It's works

Regards,

[Anonymous]

why dont we use expression grouping for this , and the result will be the same

just an thought , i am loving all your posts and saving them in pdf for future reference

Thanks for all the efforts , looking for  post on previous function

[Anonymous]

Robert,  Interesting - never thought of using it like that.

Although I have used it in with Drill-Down groups in Chart show / hide expressions so that a graph displays for the top level and then on drill down displays a details table.

[evan_kurowski]

Yeah, the syntax checker should only be trusted to validate the most straightforward of expressions.

While syntax checking can in some cases indicate an error, any usage of it as an absolute guideline for evidence of non-functionality could be very, very wrong.  (Most apps I see these days, fully verifiable syntax in the expressions are the exception and not the norm)

Love those cyclics!!! Gooooo Qlik!