Solved: Re: how to show 6 months - Qlik Community

Luke_Killer_IT · ‎2022-08-04

hello,

I have a problem because I would like to see the prices of 6 months as you can see in the screenshot I have only one price from this year missing 5 from the previous year

this is the script I use to display prices

Max({<gueltig_ab={">$(=AddMonths(Max(gueltig_ab),-6))"}>}Preis)

marcus_sommer · ‎2022-08-05

Could it be that you don't want to show the last 6 month else the last 6 price-entries? In this case you may try to use max(Date, 6) to reference to the appropriate date in the past.

But more likely is probably that you want respectively need (you didn't ask directly in this direction) a general resolution of the price-table against your fact-data and with it comes then a connection to the period-fields from a master-calendar. To get something like this you will need to do several additionally things:

creating a new Gueltig_bis field which contained the value from the previous Gueltig_ab value unless for the most current ones which may get today()
based on the above you could apply some intervalmatch-logic with it against a date to get a price-value for each single date
this price-information could then be joined/mapped against the facts and maybe in further steps multiplied with amount/tax/discount information and/or making differences to any other available price information

This means that now for each date and therefore derived to any needed period-field are directly prices available and you may now apply min/max/avg and/or a count(distinct) and/or a weighted averages logic to show the development of the prices over the time.

Of course it's not a matter of a few minutes else it may require a complete re-design of the data-model + UI from your application but in the long term it would very powerful within the analysis.

- Marcus

View solution in original post

sidhiq91 · ‎2022-08-04

@Luke_Killer_IT Not sure about you requirement though, as per your requirement, it will max price of the last 6 months.

Could you please provide some sample data?

Luke_Killer_IT · ‎2022-08-04

@sidhiq91

if it helps you I can give you the next dates with the prices that should occur at most 6 months back

sidhiq91 · ‎2022-08-04

@Luke_Killer_IT Yes please provide in the excel format. Just a sample. I will help you out.

Luke_Killer_IT · ‎2022-08-04

@sidhiq91 please this is the data I use

sidhiq91 · ‎2022-08-04

@Luke_Killer_IT If I understand correctly you need to find the Max of price in the last 6 months right?

Max({<Gueltig_ab={">=$(=Addmonths(Max(Gueltig_ab),-6))"}>}Preis)

Backend:

Temp:
LOAD
Teile,
TeileArt,
LagerMe,
Bezeichnung1,
Date(Gueltig_ab,'MM/DD/YYYY') as Gueltig_ab,
Num(left(Preis,5)) as Preis,
ME,
TeileGruppe
FROM [lib://Qlik Community Practice/Last 6 Months.xlsx]
(ooxml, embedded labels, table is Sheet1);

Exit Script;

Luke_Killer_IT · ‎2022-08-04

@sidhiq91 yes,you understand

Luke_Killer_IT · ‎2022-08-05

@sidhiq91 and have you come up with any ideas because I completely don't know how to do it anymore 😞

sidhiq91 · ‎2022-08-05

@Luke_Killer_IT I have already given you the expression which will give you the Max value from the last 6 months. If that is not what you are looking for then you can give me the expected output and i will try to help you out.

Luke_Killer_IT · ‎2022-08-05

@sidhiq91

should end with a maximum of 6 months from the maximum date and then displays all months

how to show 6 months

Application Development

Layout & Visualizations

Other

Scripting