topic Re: Creating Dynamic Master Items in QlikView

Creating Dynamic Master Items

Fri, 20 Nov 2015 14:36:11 GMT

Hi Guys,

I have a question:

I have uploaded in my app a sales record with some attributes as day, product sold, quantity, price….(all columns).

Day       Product             quantity   price
1 Nov   Red shoes           2             7$
1 Nov   Blue shoes          1             2$

...

2 Nov

3 Nov

....

I have created a master item called months where I add all the days of the month,

And a master item called week, where I add the weeks.

Now, what I want to do is to create three different measures

1) Last 30 days

2) Last 7 days

3) Last 3 days

Basically what I would like to do is to put these three measures in a table as column, in a way that I can see something like

PRODUCT Last 30 days last 7 days last 3 days

Black shoes sold 25 6 2

Red shoes sold 95 12 7

............

And so on.

The goal would be to create “Auto updating master measures”

This means that every day, when I replace the existing doc with an updated version, my app should be able to scan all the days in the “days” column, find the last 30, last 7 and last 3, and update the master measures.

The product column is then calculated including the master measure in the calculation and it’s automatically updated.

Is this possible?

Thank you in advance

Re: Creating Dynamic Master Items

Anonymous — Fri, 20 Nov 2015 15:06:28 GMT

See these posts about rolling period calculations:

Set Analysis for Rolling Periods

Re: Set Analysis Rolling Period

Re: Creating Dynamic Master Items

Mark_Little — Fri, 20 Nov 2015 15:11:59 GMT

Hi,

Something like the below in set analysis

SUM({<Day={'<=Today(),>=Today()-3}>}quantity)

You have to be careful of the date formats though.

Mark

Re: Creating Dynamic Master Items

Fri, 20 Nov 2015 15:19:51 GMT

Hi Mark,
Thanks for your reply,

the thing is that I export a table with the first column like

Day

1/1/15

1/2/15

1/3/15

1/4/15

1/5/15

so I have first to say to the app what is "today", which should be the "highest" value of the column

From it, creating a dynamic item called "Last 7 days" that can basically fill a table like

Day
....
...
...
...

...

today

Same for last 3 days

Day

....

...

Today

You see what I mean?

Best Regards,

Re: Creating Dynamic Master Items

Mark_Little — Fri, 20 Nov 2015 15:29:47 GMT

You could try MAX(Date) instead of the today().

Another option it is to do something in script.

ThreeDay:

Load

Product,

SUM(quantity) as 3dayQTY,

SUM(price) as 3dayPrice

Resident 'Your table'

where Date > MAX(Date) - 3

GROUP BY Product;

Mark

Re: Creating Dynamic Master Items

Fri, 20 Nov 2015 15:33:27 GMT

Thanks I will have a look now!

Re: Creating Dynamic Master Items

Fri, 20 Nov 2015 15:36:28 GMT

Hi Mark,

This is a good idea but I guess it would be quite difficult since I have to put together data coming from different files and match them in the app

But I will give it a try if I don't find other solutions!

Cheers

Re: Creating Dynamic Master Items

Fri, 20 Nov 2015 15:39:46 GMT

You can create expression for each measure in straight table like below:

Assume you have date field in your Master Items table

Last 30 Days : Sum({DateField={">=$(=Max(DateField)-30)<=$(=Max(DateField))"}}quantity)

Last 7 Days : Sum({DateField={">=$(=Max(DateField)-7)<=$(=Max(DateField))"}}quantity)

Last 3 Days : Sum({DateField={">=$(=Max(DateField)-3)<=$(=Max(DateField))"}}quantity)

Re: Creating Dynamic Master Items

Fri, 20 Nov 2015 16:31:14 GMT

Hi,

Thanks so much, I feel that I am close to what I want but it still doesn't work.

I'd like to point this out

All the products, the spend and all the other attributes are already aggregated by day.
It means that I am already able to plot a table where all the data are aggregated (with sum)

I can already have smth like

Date Product Quantity Price per unit Total Price Shop
1 Nov Blue shoes 4 30 120 London

1 Nov Red shoes 5 30 150 Chelsea

2 Nov Blue shoes 2 30 60 London

............

...........

.........

what I want to do is only to manipulate the date column

so I want to be able to plot 3 tables at the same time

last 3

last 7

last 30

so I can have in real time 3 separate tables with a manipulate date column, and the rest (should) come automatically.

Hope it's clear

(I am quite new with Qlik so sorry if it may look a very basic question)

Best

Re: Creating Dynamic Master Items

Mon, 01 Feb 2016 08:56:48 GMT

Hi,

I would like to show only data for last 7 days of last week, as per my requirement my weekstarts from Friday and ends on Thursday, so currently for ex: today is Monday so I want to show the data from Friday 22nd January to Thursday January 28th with the measure.

My expression will be:

=Sum({Calculation} Sales)

Please find the attached screenshot for the same.

Please help on this, thanks