Hi there, I'm extremely new to Qlik and am having problems replicating what is a very easy formula (SUMPRODUCT) in Excel in order to get the weighted average of some lines of data.  Attached is the table I have created, in order to check the data is correct before I then create graphs.  I have tried copying and pasting a few formula's such as AGGR, ONLY and set expression but I must be doing something wrong. 

I need a weighted average of the ct_mic column, which should be the total sum of ct_mic*Net Kg divided by total sum of Net Kg.

I also need to know how to exclude the 0 values from this calculation so that the weighted average is correct?

Help!

Hello,

I'm facing the following problem and hope I can explain it well.

There is the following table

Table:

Item | Status | Alternative item

A       |     x        |     B

B      |     x        |     D

C     |              |      I

D    |        x        |    E

E    |                  |   H

F    |                  |  K

For this  I need the following calculation.

If Item is not available (x), there needs to be a check on the alternative item.

I need to know from each item which is the available alternative and the road to it.
Table Solution would look as following

Aggr | Item | Alternative Item

A       |    A      |   B

A      |    B      |    D

A      |    D     |     E

B    |     B      |     D

B   |     D      |     E

D   |    D      |     E

So the check is only done for items that are not available untill first available substitute is found. The items won't be in order, because can replacements can be added any time and removed at any time too (so i dont think peek or previous can be used).

So  an applymap() will be used to find the replacement item. This data is available (in my eyes basically the table itself, because all this information is in the table).

My idea was to start with put filter on table to only check if status is 'x' and where there is an alternative item

Then comes to outerloop,  (for each loop).

For each vItem in FieldValueList('Item') (this is to check for all items which arent filtered out and to get the field: Aggr

But then comes my problem, how can I add her a do while loop. To keep searching till condition is met (applymap doesnt have a postitive result). My idea was to have vItem to aggr column and also items for the first loop (row) which gives me a replacement item with applymap on the input.

Then this needs to be saved in a variable to get the replacement item for that current item. (then row 1 is done)

And after that the replacement item needs to moved to a new row in the item column to use as input again for the applymap() (row 2) untill  there is no replacement is found.  After that u go to the next item in Item column and start it all over again till all Items are done

Is this possible and how would I need to write this? My main problems are:

- how to set the variables so they can be used in the loop and that after row 1 the outcome from the mapping will become item of next row and by that also the input of the next mapping iteration untill conditon is met and broken out of innerloop.

- How to nest the do while loop in the for each loop.

Below location has logs only for today. Not sure where to find log file for past few weeks?

C:\ProgramData\Qlik\Sense\Log\Repository\Trace

We basically want to know who changed what setting within QMC --> User Directory Connector.

Hello everyone,

I'm new to this community (and to Qlik as well). I would like if I can run my own SQL queries and how to do that with the couple of data connectors I already created.

Thank you very much,

Emmanuel

Hello everyone,

I came accross a very strange behaviour below:-

I have below dimensions and measures :-

Dimension:- =valuelist('Direct labour N-1', 'Indirect Labour N-1','Total SG&A + R&D N-1')

Measure:-

if(valuelist('Direct labour N-1', 'Indirect Labour N-1','Total SG&A + R&D N-1')='Direct labour N-1',

num(sum({<Indicateur.Code = {'PO.1.161b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
/

sum({<Indicateur.Code = {'PO.1.137b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"}>} [Indicateur.Value])
,'###.#%'),

if(valuelist('Direct labour N-1', 'Indirect Labour N-1','Total SG&A + R&D N-1')='Indirect Labour N-1',
num(sum({<Indicateur.Code = {'PO.1.162b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
/

sum({<Indicateur.Code = {'PO.1.137b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
,'###.#%'),

if(valuelist('Direct labour N-1', 'Indirect Labour N-1','Total SG&A + R&D N-1')='Total SG&A + R&D N-1',

num(sum({<Indicateur.Code = {'PO.1.207b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
/
sum({<Indicateur.Code = {'PO.1.137b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
,'###.#%')

)

)
)

When i plot the above in bar chart , i get correct number s:-

But when i created a pie chart, it is showing some additional values like below:-

In the above  chart, the number corresponding to label 'Values-14.0%' is correct. However, there is an additional label named 'share-17.0%' which i have not calculated but somehow it is appearing out of nowhere.

I am unable to understand why exactly it is working fine in bar chart and causing problem in pie chart.

Can anyone please help me if with this ? 😞

Hello everyone and welcome to the new Ideation Discussion Board!

The Product Insight & Ideas blog has been retired and replaced by the Ideation Discussion board. This will allow the Ideation community to all post and comment on discussions related to the Ideation process. The Ideation rules from the original blog were reposted here and pinned to the top.

A few rules before you post:

1. Please do not post ideas or reference your posts here. This is not a place to "prioritize" your ideas. Please remember that all ideas are put through to our app and reviewed by product managers, and the best way to "plug" your ideas is to like and comment on the Ideation post.
2. Please do not use this as a complaints board. We would love to address your concerns and critiques here, but if you have serious issues, please reach out to your customer success, account reps, or the ideation moderator. 
3. As with the Ideation boards, do not post private company information as these posts are viewable by the public.
4. Do not spam with irrelevant or excessive content.

So, check it out and start posting! I can't wait to engage with you all.

Meghann

I've looked at several posts looking at counting instances and mine seems unique. It pertains to units messaging (aka "On Air")and I need to identify units that repeatedly message over a period of time including the latest time period (current). Query periodicity is weekly. Each query fetching unit data returns data such as serial number and last message time, plus customer and other info. Most units message multiple times per day but I only fetch the latest each query. I suspect I could count the times a unit shows up over a known period of time and count that number of queries and if the counts match plus it exists in the latest query that would give me the number of current continually messaging units. I also suspect it would work dynamically where if I open the period to say 10 periods and the unit messaged 10 times and existed in the latest period would also exist in all periods under 10. I think it can all be done in set analysis and no extra aggregation in the scripts.

Has anyone done something like that before and can say whether this is the simplest method or would there be another method better suited?

V/r,

John

Hi there

I am struggling to figure out how to count the number of customers that purchased more than 3 times in the past 12 months. The result given by the expression below is empty.

COUNT({<
[Customer ID] = {"=sum({<

[Type]={'P'}, Date={">=$(=date(addmonths(Max(Date),-12),'YYYY-MM-DD'))<=$(=date(Max(Date),'YYYY-MM-DD'))"}
 

[Total Events])>=3"}

                  >}distinct [Customer ID])

Please help 

 March 2021, a year after the world forever changed. It’s so hard to believe it’s been a year already, yet at the same time, it feels like a lifetime. This year we relied more heavily on digital communication than ever before and there was no shortage of communicating in the Qlik Community. One member that stood out to us -- standing by to help, sharing his expertise, and leading by example -- is Qlik Community MVP Lech Miszkiewicz. That is why he is our featured member this month! Lech is in the Community almost every day. He has posted over 4K times, provided countless solutions, and received close to 2K likes.

Lech was born in Poland and lived there most of his life. He earned a Master of Engineering in Commodity Science at the University of Economics and separately a Bachelor of Corporate Management. Lech is married and lives in Sydney Australia. He loves watching Formula 1 and is a big fan of Cirque du Soleil. Lech plays guitar and keyboard. He loves to travel, take photos, and collects wristwatches. If you can believe it, he also runs half marathons and swims! So cool!

Lech began his professional career at Solaris Bus & Coach where he learned QlikView. He later became a QlikView consultant and started working for Hogart Business Intelligence – a Polish Qlik partner. This is where his passion for Qlik started.

In 2015 he was hired by Australian Qlik Elite partner InsideInfo Pty Ltd. and moved to Australia. Lech is still there today and strives to provide good service to InsideInfo’s clients. Lech would like to give a shout-out to his bosses Phil and Stuart!

As a Qlik consultant, Lech works with QlikView, Qlik Sense, NPrinting, and other Qlik products. He spends time in the Qlik Community consuming information from Support, NPrinting, Ideas, and the Tech Preview forums to keep up with the important things. He spends most of his time in the NPrinting forum and leaves the other forums to the likes of Sunny T, Vegar, and many other amazing contributors. Lech recognized that many members experienced the same NPrinting challenges, and so was born his NPrinting blog: https://nprintingadventures.com.

We are so happy and grateful that Lech is part of the Qlik Community. Please join us and recognize him with a “Round of Applause” by leaving a comment and giving a Like to show Lech how much we appreciate him. @Lech_Miszkiewicz @Melissa_Potvin 

Hi All, 

I have multiple sheets in my application. all the sheets should show past 2 days data upon opening the application. (we have a filter pane object created for this). The field for this list of values are derived from the script written in the backend.

There is two timestamp fields involved (ALL sheets but one uses different timestamp field to get its values).

Now the problem is whenever there is a selection made in filter pane  say Filter1, it affects the Filter2 on the other page as well. This should not be the case. I have tried using Alternate state but not succeeded. 

Is there any alternative method to achieve this?  

Thank you!