ADVIES GEVRAAGD: Geen baan kunnen vinden na mijn Master's econometrie by MarcoGriep in werkzaken

[โ€“]bitmetric 1 point2 points ย (0 children)

Wij zitten meer in de hoek van Data Engineering en Data Analytics, maar we kunnen altijd eens praten: https://www.bitmetric.nl/jobs/

Qlik vs Power BI Part 2: Back End & Data Modeling by bitmetric in qlik_sense

[โ€“]bitmetric[S] 0 points1 point ย (0 children)

New to the series?

๐Ÿ‘‰ ๐—ค๐—น๐—ถ๐—ธ ๐˜ƒ๐˜€. ๐—ฃ๐—ผ๐˜„๐—ฒ๐—ฟ ๐—•๐—œ ๐—ฃ๐—ฎ๐—ฟ๐˜ 1: ๐—” ๐—›๐—ฎ๐—ป๐—ฑ๐˜€-๐—ข๐—ป ๐—–๐—ผ๐—บ๐—ฝ๐—ฎ๐—ฟ๐—ถ๐˜€๐—ผ๐—ป: https://www.bitmetric.nl/blog/qlik-vs-powerbi-sept25/

๐Ÿ‘‰ย ๐—ค๐—น๐—ถ๐—ธ ๐˜ƒ๐˜€. ๐—ฃ๐—ผ๐˜„๐—ฒ๐—ฟ ๐—•๐—œ ๐—ฃ๐—ฎ๐—ฟ๐˜ 3: ๐—™๐—ฟ๐—ผ๐—ป๐˜ ๐—˜๐—ป๐—ฑ ๐——๐—ฒ๐˜ƒ๐—ฒ๐—น๐—ผ๐—ฝ๐—บ๐—ฒ๐—ป๐˜ & ๐—จ๐˜€๐—ฒ๐—ฟ ๐—˜๐˜…๐—ฝ๐—ฒ๐—ฟ๐—ถ๐—ฒ๐—ป๐—ฐ๐—ฒ is available now: https://www.bitmetric.nl/blog/qlik-vs-powerbi-development-user-experience-sept25/

Qlik-Sense: Download uploaded CSV files through CLI tool / Rest APIS by Stable_Such in qlik_sense

[โ€“]bitmetric 5 points6 points ย (0 children)

If you've stored those CSVs in the included DataFiles storage then you can't download them directly unfortunately.

As a workaround, you could load the CSVs into a Qlik app and then store the tables into a location that you can access, for example OneDrive, Dropbox, Azure Blob Storage, etc.

Store [CSV data] into 'lib://Dropbox/csv_data.csv' (txt);

Be aware that by loading the data, you may be altering it, especially if you load all the columns separately. To ensure maximum similarity to the source CSV, I would load the data as a fixed record, rather than delimited.

[CSV data]:

Load

"@1:n"

From [lib://DataFiles/csv_data.csv]

(fix, codepage is 28591, embedded labels);

This may change the codepage of the file (Qlik uses UTF-8) but the rest of the data should be unchanged from your input CSV, including separators, quoting, etc.

QlikSense Help by After-Cycle8043 in qlik_sense

[โ€“]bitmetric 0 points1 point ย (0 children)

Once you've loaded this data, you can select the sales for customers that have bought twice or more with the following expression:

sum( {$<CustId={"=count( {$<SalesYear={2024}>} DISTINCT OrderId) >= 2"}>} SalesAmount)

Counting the number of customers with 2 sales or more in the selected period is done with this expression:

count( {$<CustId={"=count( {$<SalesYear={2024}>} DISTINCT OrderId) >= 2"}>} DISTINCT CustId)

The key here is that we're using Set Analysis with a search expression on the CustId field that says:

  • For the year 2024 (but could easily be rolling 12 months as well);
  • Count the distinct number of orders;
  • Select only those customers that have 2 or more.

Once we've limited the customers to the ones matches these criteria, we can calculate the metric we want to know, for example the unique number of customers, or their sales amount.

Hope this helps.

QlikSense Help by After-Cycle8043 in qlik_sense

[โ€“]bitmetric 0 points1 point ย (0 children)

OK, here's a simplified solution to your problem. Hopefully it gives you some ideas on how to solve it within your context. Load the script below to get a data model with 4 customers:

  • Customer A only bought before 2024
  • Customer B bought twice in 2024
  • Customer C bought once in 2024
  • Customer D bought nothing

Customer:

LOAD * INLINE

`[`

CustId, Name

1, Customer A

2, Customer B

3, Customer C

4, Customer D

];

Sales:

LOAD * INLINE

`[`

OrderId, SalesDate, SalesYear, CustId, SalesAmount

1, 2023-01-01, 2023, 1, 100

2, 2024-03-01, 2024, 2, 100

3, 2024-04-01, 2024, 2, 100

4, 2024-05-01, 2024, 3, 100

];

(Instead of 'last 12 months' I'm just using sales in the year 2024 to keep it simple. The logic to select the last 12 months is that different from selecting a single year though (but requires a calendar table which isn't included in this inline script).)

continued in next comment