Welcome to Qlik Community

Hi!

I am stuck with a task. Please, help!

I have the next data:

SourceData:
Load * inline [SetName, Date, Type, Value, color
'A',	30/10/2020,	Type_1,	1,  red
'A',	26/10/2020,	Type_1,	2,  red
'B',	30/10/2020,	Type_1,	3,  green
'B',	26/10/2020,	Type_1,	,   green
'C',	30/10/2020,	Type_1,	2,  red
'C',	25/10/2020,	Type_1,	3,  red
'D',	25/10/2020,	Type_1,	4,  green
'A',	30/10/2020,	Type_2,	4,  red
'A',	26/10/2020,	Type_2,	3,  red
'B',	30/10/2020,	Type_2,	,   green
'B',	26/10/2020,	Type_2,	,   green
'C',	30/10/2020,	Type_2,	1,  red
'C',	25/10/2020,	Type_2,	5,  red
'D',	25/10/2020,	Type_2,	2,  green
'E',	25/10/2020,	Type_2,	,   green
'E',	26/10/2020,	Type_2,	,   green
'E',	25/10/2020,	Type_1,	,   green
'A'	,	  ,     Type_3,	,   black
'B'	,	  ,     Type_3,	,   black
'C'	,	  ,     Type_3,	,   black
];

I have a Pivot Table where I calculate Count of Values. And filter by Color.

I want to display all possible types always, but SetName should be filtered by Color.

For example, when I set Color = "Black" I want to see the next result: 

I use two expressions:

1) Count( Value ) + 0 * Count( {1-1<color={"$(=(color))"}>} 0). Types display correctly, but  I see ALL values of SetName

2) Count(Value). SetNames dysplay correctly, but Types are missing (See screenshot below)

I marked the result what I expect in Yellow.

Do you have any ideas on How to solve this?  Any help is appreciated!

Hi All, 

I have a requirement to create a line chart in qlik sense with three dimensions and one measure as shown below:

Is it possible to create this chart in qlik sense? What would I need to do to create this chart? 

Thanks, 

Aswin Narayanan

Hi, community

I've got a table with clients debts looking like this

I want to make a measure, calculating average debt over dates, and then sum it over customers.

The obvious way is to use Aggr:

My_Measure = sum(aggr(sum([Debt]) / count(distinct Date), Customer))

and it works fine for a table with Customer as a dimension:

But if I change the Dimension to [Date], I get the wrong result, since Aggr aggregates Date before it looks for Dimension in Table:

Of course, I can rewrite measure like this

sum(aggr(sum([Debt]) / count(distinct Date), Date, Customer))

but in this case the Total result does not work correctly (it sums to 340)

Is there any workaround, how to make this measure work correctly with Customer OR Date as dimension, and woth correct Totals?

And can I make ot work corectly with any dimensions from linked tables (for example, Month, Year, Customer_Region etc._)

Thanks!

Hi Masters:

I now have data from Jan 19 to Oct 20, presenting as 9/1/2020.

What if I only want to show the latest 12 month/ moving annual months: Nov 19 to Oct 20 at my line chart

what set expression I should put? or any smart way?

Thanks

Dear community,

i try to filter a dimension within a chart.

Lets say i want to count packages for weight classes in a bar chart.

Despite al lot of searching i did not find syntax to filter packages between 5 and 10 kilogram.

One of my attempts:

{<pack_max_weight={'<10'},pack_max_weight={'>5'}>}pack_max_weight

This filter shall lie beyond all other filters. That means the resulting set can be reduced even more by other filters.

Thank you in advance

I have a requirement where I need to show a graph of the number of new visits per month for a study

So for a Study A, for visits we have to use the visit types which are shown as:

V0 

V1

V2

V3

V4

V0 for example would be the new visits that I am needing to use for my chart. Other studies have different visit types for new visits like T0, or V1. What expression can I use to show the count of the first visit type? 

For example, for study A if I take count of v0 for Jan I should get 24, but with the expression that I am using: 

=Count({$<visittype={$(=min(visittype))}>}visittype)

I am getting 0, not 24. Is there something wrong with the expression that I am using? 

In the image below, there is a Client Code that is showing the variable vFinanceiro. because I need to create a set analysis that doesn't come white.

The result I need should look like this.🤔

I'm able to load a table that is a list of Excel files in a SharePoint location. This is all done via Web Connectors. The code and results look like this:

let vQwcConnectionName = 'lib://General sharepoint connector (userID123)';

Office365Connector_ListFiles:
LOAD
           UniqueId as ListFiles_UniqueId,
           id as ListFiles_id
FROM [$(vQwcConnectionName)]
(URL IS [http://qlikselfservice:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=%2fsites%2fABC20140212073005&folder=%2fsites%2fSTS20140212073005%2fMSQR%2f2.+Reporting%2f2020-04&maxResults=40&appID=&loadAccessToken=mcd2x4lxzk], qvx);

I want to combine all these Excel files into one giant master table in Qlik Sense. New files are added to the SharePoint location monthly, and would need to automatically be appended to the end of the master table. My thoughts on doing this were:

for each file in filelist('lib://vQwcConnectionName/path/to/directory/*.xlsx')
load * from [$(file)](xlsx);
next file

Unfortunately, this does not work. How can I load this list of Excel files and bind the rows into a giant master table?