article Did you know..? Cyclic Group in Member Articles

Did you know..? Cyclic Group

robert_mika — Tue, 20 Sep 2022 17:59:53 GMT

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

];

GUI:

Create new bar chart

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)

Re: Did you know..? Cyclic Group

marcus_malinow — Thu, 17 Dec 2015 10:55:56 GMT

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'))

Re: Did you know..? Cyclic Group

sacosta5 — Thu, 17 Dec 2015 15:28:56 GMT

Thanks...It's works

Regards,

Re: Did you know..? Cyclic Group

Anonymous — Mon, 15 Feb 2016 07:41:24 GMT

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

Re: Did you know..? Cyclic Group

Anonymous — Mon, 02 May 2016 17:55:24 GMT

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.

Re: Did you know..? Cyclic Group

evan_kurowski — Tue, 03 May 2016 20:54:05 GMT

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!