Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Same Store Sale Indicator

Hi, I´m trying to create a SSS indicator (on a pivot table) to compare sales 364 days after the selected date, something like:

sum sales if 364 day ago Sales<>0

How can I create this value? please any help is more than welcome.

Thanks,

Camilo

21 Replies
Highlighted
Contributor III
Contributor III

It does work on the example!! but it seems that a triple if is too much for my qvw it doesn´t show any data just a red cross on the pivot table

Highlighted
MVP
MVP

That's too bad

Well, if the 52 weeks are a fixed parameter, I guess we could really use a flag in the script.

It would be a bit more complicated than a simple StoreOpenDate, we have to calculate the Sales sums per Store and Date, check for any given Date the sums 52 weeks back and there we go, maybe like this:

SSSSales:

LOAD Date as Date,

Local,

Local &'-'& num(Date) as LocalDate,

sum(Sales) as SumSales

resident Input group by Local, Date;

SSSDates:

left join (Input) Load Date, Local,

if (Lookup('SumSales','LocalDate',Local&'-'&num(Date-364),'SSSSales')>0, 1) as SSS

Resident SSSSales;

drop table SSSSales;

Please have a look at attached sample, I added a second store too ensure we got the SSS correct per Store (look at the demo date in load script, only 31.8./1.9.2010 differing).

Hope this helps,

Stefan

View solution in original post

Highlighted
Contributor III
Contributor III

It worked perfectly!!!!

The loading time went from 2:30 to 7:30 but then I can use swiftly the qv model

Thanks Again Stefan!!!

Camilo

Highlighted
Contributor III
Contributor III

Stefan,

It is posible to add a condition to restrict the SSS so it doesn´t include last year sales if this year sale were equal 0.

Something like:

SSSSales:

LOAD Fecha as Fecha,

ID,

ID &'-'& num(Fecha) as LocalDate,

sum(Venta6.0) as SumSales

resident Stage1.2 group by ID, Fecha;

SSSDates:

left join (Stage1.2) Load Fecha, ID,

if ((Lookup('SumSales','LocalDate',ID&'-'&num(Fecha-364),'SSSSales')>0) and (ID&num(Fecha), 'SumSales')>0 , 1) as SSS

Resident SSSSales;

drop table SSSSales;

Highlighted
MVP
MVP

Hi Camilo.

That's quite simple, since in SSSDates, we are already having the data you need at hand, just use 'and SumSales>0':

SSSDates:

left join (Input) Load Date, Local,

if (Lookup('SumSales','LocalDate',Local&'-'&num(Date-364),'SSSSales')>0 and SumSales >0, 1) as SSS

Resident SSSSales;

drop table SSSSales;

But in your table, if SumSales =0, it will be removed anyway from the table, won't it? Or do you need a count of SSS?

Or have I missed something in your question?

Highlighted
Contributor III
Contributor III

I don´t need a count of SSS.

I tryed using: if (Lookup('SumSales','LocalDate',Local&'-'&num(Date-364),'SSSSales')>0 and SumSales >0, 1) as SSS

But I don´t see any diference on the Sales totals and I´m sure that some days this year didn´t had any sales.

Maybe SumSales>0 isn´t differentiating by Local?

Highlighted
MVP
MVP

It seems that I don't understand your requirement...

If I use above line in the load and set a Sales on an abitrary date to zero, this date will be SSS flagged '0'. Same with the dates with SumSales = 0 364 days before.

But anyway (regardless if SSS is set to 1 or 0 on these days), if the sum of Sales on these dates is zero, how should they influence the Sales totals?

Could you give me an example, maybe together with an update of the inline load of my last script?

Regards,

Stefan

Highlighted
Contributor III
Contributor III

Hmm I´m puzzled right now, I just try what I was talking about on your example and it does work without any problems!!

A 0 sales today influences the SSS in that I wont consider the sales 364 days before for that store.

I´ll try to figure out what is going on to ask with more details.

Thanks Stefan!

Camilo

Highlighted
Contributor III
Contributor III

I´m trying to get a better understanding of how this work doing somehing similar to another model wich has only years and months (in number format and separate fields). I´m trying to create a sss, but y got a script error sayin that it has an Invalid expression.

Do you know what could be wrong?

SSSSales:

LOAD AñoMes as AñoMes,

Año,

Mes,

Local,

Local &'-'& num(AñoMes) as LocalDate,

sum(Real) as SumSales

resident Data group by Local, AñoMes;

SSSDates:

left join (Data) Load AñoMes, Local,

if (Lookup('SumSales','LocalDate',Local&'-'&(Año-1)&'-'&(Mes),'SSSSales')>0, 1) as SSS

Resident SSSSales;

drop table SSSSales;

------------------------------------

PS.

Año=Year

Mes=Month

Año &'-'& Mes as AñoMes,

Thanks,

Camilo

Highlighted
MVP
MVP

Yes,

if you use group by, you can only use fields stated in the group by clause as stand alone fields in the load, so in your example, Ano and Mes are not allowed standalone. You could use them in an aggregation function, though.

maybe add Ano and Mes to your group by clause?