9 Replies Latest reply: Feb 1, 2016 3:58 AM by pavan ch

# Creating Dynamic Master Items

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?

• ###### Re: Creating Dynamic Master Items

See these posts about rolling period calculations:

Set Analysis for Rolling Periods

Re: Set Analysis Rolling Period

• ###### Re: Creating Dynamic Master Items

Thanks I will have a look now!

• ###### Re: Creating Dynamic Master Items

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

Hi Mark,

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,

P

• ###### Re: Creating Dynamic Master Items

Hi

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

Another option it is to do something in script.

ThreeDay:

Product,

SUM(quantity)      as 3dayQTY,

SUM(price)          as 3dayPrice

where Date > MAX(Date) - 3

GROUP BY Product;

Mark

• ###### Re: Creating Dynamic Master Items

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

P

• ###### Re: Creating Dynamic Master Items

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

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

P

• ###### Re: Creating Dynamic Master Items

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.